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?