Re: PHP PDO getting data from pgSQL stored function

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]



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
as simple query? If so, what's the expected output?

Regards,

--
Yasuo Ohgaki
yohgaki@xxxxxxxxxx

[Index of Archives]     [Postgresql General]     [Postgresql Admin]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Yosemite Backpacking]     [Postgresql Jobs]

  Powered by Linux