Re: PHP PDO getting data from pgSQL stored function

Hi=20

basically create a function returning a table, then select * from function(=
) can be called from php.

below is a complete sql language function i wrote returning a table.

create or replace function
  show_privilege(p_grantee name)
returns
  table(grantee name
       ,role_name name
       ,grantor name
       ,table_catalog name
       ,table_name name
       ,privilege_type varchar)
as $$=20=20=20=20
  select=20
    AR.grantee::name
    ,AR.role_name::name
    ,RTG.grantor::name
    ,RTG.table_catalog::name
    ,RTG.table_name::name
    ,privilege_type=20
  from=20=20
    information_schema.applicable_roles AR
    left outer join=20
      information_schema.role_table_grants RTG on (AR.role_name =3D RTG.gra=
ntee)
  where=20
    AR.grantee =3D p_grantee;
$$ language sql;

you'll notice the returns table defines the rows in the return.

on one of my databases, if i run:
select * from show_privilege('wuggly_ump_admin');
i get
     grantee      | role_name | grantor | table_catalog | table_name | priv=
ilege_type=20
------------------+-----------+---------+---------------+------------+-----=
-----------
 wuggly_ump_admin | sys_user  |         |               |            |=20
(1 row)


i hope that helps.

reiner


On 2 nov 2013, at 16:54, Michael Schmidt <[email protected]> wrote:

> Hi guys,
>=20
> i need to do a ugly select which i dont want to place in my php code.
> I use PDO all the time.
>=20
> I want to return a structure which is the same as a table i have created.
>=20
> I want to write it in pgSQL.
>=20
> Now my question, how can i return that and access it with PHP PDO?
> Should I use a cursor or shoul I use the return of the function (if it is=
 possible)?
>=20
> Can someone provide a piece of example code?
>=20
> Thanks for help
>=20
>=20
> --=20
> Sent via pgsql-php mailing list ([email protected])
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-php



--=20
Sent via pgsql-php mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-php