Re: PHP PDO getting data from pgSQL stored function

Hi Reiner,

On Mon, Nov 4, 2013 at 3:35 AM, reiner peterke <zedaardv(at)drizzle(dot)com> wrote:

> 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 $
> select
> AR.grantee::name
> ,AR.role_name::name
> ,RTG.grantor::name
> ,RTG.table_catalog::name
> ,RTG.table_name::name
> ,privilege_type
> from
> information_schema.applicable_roles AR
> left outer join
> information_schema.role_table_grants RTG on (AR.role_name =
> RTG.grantee)
> where
> AR.grantee = 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 |
> privilege_type
>
> ------------------+-----------+---------+---------------+------------+----------------
> wuggly_ump_admin | sys_user | | | |
> (1 row)
>
>
> i hope that helps.
>

I'm not sure what is your problem, but it seems all you need is VIEW if you
have
problem with your stored procedure.

Anyway, I suppose PostgreSQL returns result set resource. Are you saying
PDO pgsql wouldn't? or missing columns? How about pgsql module? Does it
work?
I guess you have been tested, but the "select" in procedure works as
expected
as simple query? If so, what's the expected output?

Regards,

--
Yasuo Ohgaki
yohgaki(at)ohgaki(dot)net