From: reiner peterke <zedaardv@drizzle.com>
To: Michael Schmidt <css.liquid@gmail.com>
Cc: pgsql-php@postgresql.org
Subject: Re: PHP PDO getting data from pgSQL stored function
Date: Sun, 3 Nov 2013 19:35:01 +0100
Message-ID: <FEEB7BC9-65B3-4647-A171-0CC597562D7D@drizzle.com> (raw)
In-Reply-To: <5275203C.2090007@gmail.com>
References: <5275203C.2090007@gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-php@postgresql.org
Cc: zedaardv@drizzle.com, css.liquid@gmail.com
Subject: Re: PHP PDO getting data from pgSQL stored function
In-Reply-To: <FEEB7BC9-65B3-4647-A171-0CC597562D7D@drizzle.com>
References: <5275203C.2090007@gmail.com> <FEEB7BC9-65B3-4647-A171-0CC597562D7D@drizzle.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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