pg.ddx.io  pgsql-php@postgresql.org mailing list archive  
help / color / mirror / Atom feed
PHP PDO getting data from pgSQL stored function
5+ messages / 4 participants
[nested] [flat]

* PHP PDO getting data from pgSQL stored function
@ 2013-11-02 15:54 Michael Schmidt <css.liquid@gmail.com>
  2013-11-03 16:06 ` Re: PHP PDO getting data from pgSQL stored function Jonah H. Harris <jonah.harris@gmail.com>
  2013-11-03 18:35 ` Re: PHP PDO getting data from pgSQL stored function reiner peterke <zedaardv@drizzle.com>
  0 siblings, 2 replies; 5+ messages in thread

From: Michael Schmidt @ 2013-11-02 15:54 UTC (permalink / raw)
  To: pgsql-php

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 (pgsql-php@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-php



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: PHP PDO getting data from pgSQL stored function
  2013-11-02 15:54 PHP PDO getting data from pgSQL stored function Michael Schmidt <css.liquid@gmail.com>
@ 2013-11-03 16:06 ` Jonah H. Harris <jonah.harris@gmail.com>
  2013-11-08 19:26   ` Re: PHP PDO getting data from pgSQL stored function Michael Schmidt <css.liquid@gmail.com>
  1 sibling, 1 reply; 5+ messages in thread

From: Jonah H. Harris @ 2013-11-03 16:06 UTC (permalink / raw)
  To: Michael Schmidt <css.liquid@gmail.com>; +Cc: pgsql-php

On Sat, Nov 2, 2013 at 11:54 AM, Michael Schmidt <css.liquid@gmail.com>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/

^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: PHP PDO getting data from pgSQL stored function
  2013-11-02 15:54 PHP PDO getting data from pgSQL stored function Michael Schmidt <css.liquid@gmail.com>
  2013-11-03 16:06 ` Re: PHP PDO getting data from pgSQL stored function Jonah H. Harris <jonah.harris@gmail.com>
@ 2013-11-08 19:26   ` Michael Schmidt <css.liquid@gmail.com>
  0 siblings, 0 replies; 5+ messages in thread

From: Michael Schmidt @ 2013-11-08 19:26 UTC (permalink / raw)
  To: Jonah H. Harris <jonah.harris@gmail.com>; +Cc: pgsql-php

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 <css.liquid@gmail.com 
> <mailto:css.liquid@gmail.com>> 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/

^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: PHP PDO getting data from pgSQL stored function
  2013-11-02 15:54 PHP PDO getting data from pgSQL stored function Michael Schmidt <css.liquid@gmail.com>
@ 2013-11-03 18:35 ` reiner peterke <zedaardv@drizzle.com>
  2013-11-03 21:31   ` Re: PHP PDO getting data from pgSQL stored function Yasuo Ohgaki <yohgaki@ohgaki.net>
  1 sibling, 1 reply; 5+ messages in thread

From: reiner peterke @ 2013-11-03 18:35 UTC (permalink / raw)
  To: Michael Schmidt <css.liquid@gmail.com>; +Cc: pgsql-php

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 <css.liquid@gmail.com> 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 (pgsql-php@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-php



-- 
Sent via pgsql-php mailing list (pgsql-php@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-php



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: PHP PDO getting data from pgSQL stored function
  2013-11-02 15:54 PHP PDO getting data from pgSQL stored function Michael Schmidt <css.liquid@gmail.com>
  2013-11-03 18:35 ` Re: PHP PDO getting data from pgSQL stored function reiner peterke <zedaardv@drizzle.com>
@ 2013-11-03 21:31   ` Yasuo Ohgaki <yohgaki@ohgaki.net>
  0 siblings, 0 replies; 5+ messages in thread

From: Yasuo Ohgaki @ 2013-11-03 21:31 UTC (permalink / raw)
  To: reiner peterke <zedaardv@drizzle.com>; +Cc: Michael Schmidt <css.liquid@gmail.com>; pgsql-php

Hi Reiner,

On Mon, Nov 4, 2013 at 3:35 AM, reiner peterke <zedaardv@drizzle.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@ohgaki.net

^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2013-11-08 19:26 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-11-02 15:54 PHP PDO getting data from pgSQL stored function Michael Schmidt <css.liquid@gmail.com>
2013-11-03 16:06 ` Jonah H. Harris <jonah.harris@gmail.com>
2013-11-08 19:26   ` Michael Schmidt <css.liquid@gmail.com>
2013-11-03 18:35 ` reiner peterke <zedaardv@drizzle.com>
2013-11-03 21:31   ` Yasuo Ohgaki <yohgaki@ohgaki.net>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox