Hi Reiner,
On Mon, Nov 4, 2013 at 3:35 AM, reiner peterke <zedaardv@xxxxxxxxxxx> 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
