pg.ddx.io pgsql-php@postgresql.org mailing list archive
help / color / mirror / Atom feedPHP 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