PHP PDO getting data from pgSQL stored function

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

PHP PDO getting data from pgSQL stored function

Michael Schmidt-2
Hi guys,

i need to do a ugly select which i dont want to place in my php code.
I use PDO all the time.

I want to return a structure which is the same as a table i have created.

I want to write it in pgSQL.

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)?

Can someone provide a piece of example code?

Thanks for help


--
Sent via pgsql-php mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-php
Reply | Threaded
Open this post in threaded view
|

Re: PHP PDO getting data from pgSQL stored function

Jonah H. Harris-2
On Sat, Nov 2, 2013 at 11:54 AM, Michael Schmidt <[hidden email]> wrote:
i need to do a ugly select which i dont want to place in my php code.
I use PDO all the time.

All database access from our application is functionalized using either PL/pgSQL or SQL functions.
 
I want to return a structure which is the same as a table i have created.

CREATE FUNCTION blah (IN param 1, ..., IN param n, OUT param 1, ... OUT param n) RETURNS SETOF RECORD

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)?

Just do a SELECT * FROM blah(...); as a normal PDO query.

--
Jonah H. Harris
Blog: http://www.oracle-internals.com/
Reply | Threaded
Open this post in threaded view
|

Re: PHP PDO getting data from pgSQL stored function

reiner peterke
In reply to this post by Michael Schmidt-2
Hi

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.

reiner


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

> Hi guys,
>
> i need to do a ugly select which i dont want to place in my php code.
> I use PDO all the time.
>
> I want to return a structure which is the same as a table i have created.
>
> I want to write it in pgSQL.
>
> 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)?
>
> Can someone provide a piece of example code?
>
> Thanks for help
>
>
> --
> Sent via pgsql-php mailing list ([hidden email])
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-php



--
Sent via pgsql-php mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-php
Reply | Threaded
Open this post in threaded view
|

Re: PHP PDO getting data from pgSQL stored function

Yasuo Ohgaki
Hi Reiner,

On Mon, Nov 4, 2013 at 3:35 AM, reiner peterke <[hidden email]> 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
[hidden email]
Reply | Threaded
Open this post in threaded view
|

Re: PHP PDO getting data from pgSQL stored function

Michael Schmidt-2
In reply to this post by Jonah H. Harris-2
Hi,

sorry for the late response.

I did it with the solution posted by Jonah.

It is not necessary for me to return a table(...) because i want to return the structure which is already defined in my database so i can use SETOF RECORD as return type. So i use the real table to define the return of my function.

PDO select works just like a normal select on a table.

Thank you.

Am 03.11.2013 17:06, schrieb Jonah H. Harris:
On Sat, Nov 2, 2013 at 11:54 AM, Michael Schmidt <[hidden email]> wrote:
i need to do a ugly select which i dont want to place in my php code.
I use PDO all the time.

All database access from our application is functionalized using either PL/pgSQL or SQL functions.
 
I want to return a structure which is the same as a table i have created.

CREATE FUNCTION blah (IN param 1, ..., IN param n, OUT param 1, ... OUT param n) RETURNS SETOF RECORD

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)?

Just do a SELECT * FROM blah(...); as a normal PDO query.

--
Jonah H. Harris
Blog: http://www.oracle-internals.com/