Re: PHP PDO getting data from pgSQL stored function

--001a11c3bb346c67e304ea4c8817
Content-Type: text/plain; charset=UTF-8

Hi Reiner,

On Mon, Nov 4, 2013 at 3:35 AM, reiner peterke <[email protected]> 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
[email protected]

--001a11c3bb346c67e304ea4c8817
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div class=3D"gmail_extra">Hi Reiner,</div><div class=3D"g=
mail_extra"><br></div><div class=3D"gmail_extra"><div class=3D"gmail_quote"=
>On Mon, Nov 4, 2013 at 3:35 AM, reiner peterke <span dir=3D"ltr">&lt;<a hr=
ef=3D"mailto:[email protected]" target=3D"_blank">[email protected]</=
a>&gt;</span> wrote:<br>

</div><div class=3D"gmail_quote"><blockquote class=3D"gmail_quote" style=3D=
"margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,2=
04,204);border-left-style:solid;padding-left:1ex"><div id=3D":21k" style=3D=
"overflow:hidden">

basically create a function returning a table, then select * from function(=
) can be called from php.<br>
<br>
below is a complete sql language function i wrote returning a table.<br>
<br>
create or replace function<br>
=C2=A0 show_privilege(p_grantee name)<br>
returns<br>
=C2=A0 table(grantee name<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0,role_name name<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0,grantor name<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0,table_catalog name<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0,table_name name<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0,privilege_type varchar)<br>
as $<br>
=C2=A0 select<br>
=C2=A0 =C2=A0 AR.grantee::name<br>
=C2=A0 =C2=A0 ,AR.role_name::name<br>
=C2=A0 =C2=A0 ,RTG.grantor::name<br>
=C2=A0 =C2=A0 ,RTG.table_catalog::name<br>
=C2=A0 =C2=A0 ,RTG.table_name::name<br>
=C2=A0 =C2=A0 ,privilege_type<br>
=C2=A0 from<br>
=C2=A0 =C2=A0 information_schema.applicable_roles AR<br>
=C2=A0 =C2=A0 left outer join<br>
=C2=A0 =C2=A0 =C2=A0 information_schema.role_table_grants RTG on (AR.role_n=
ame =3D RTG.grantee)<br>
=C2=A0 where<br>
=C2=A0 =C2=A0 AR.grantee =3D p_grantee;<br>
$ language sql;<br>
<br>
you&#39;ll notice the returns table defines the rows in the return.<br>
<br>
on one of my databases, if i run:<br>
select * from show_privilege(&#39;wuggly_ump_admin&#39;);<br>
i get<br>
=C2=A0 =C2=A0 =C2=A0grantee =C2=A0 =C2=A0 =C2=A0| role_name | grantor | tab=
le_catalog | table_name | privilege_type<br>
------------------+-----------+---------+---------------+------------+-----=
-----------<br>
=C2=A0wuggly_ump_admin | sys_user =C2=A0| =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0=
 =C2=A0 =C2=A0|<br>
(1 row)<br>
<br>
<br>
i hope that helps.</div></blockquote></div><br>I&#39;m not sure what is you=
r problem, but it seems all you need is VIEW if you have=C2=A0</div><div cl=
ass=3D"gmail_extra">problem with your stored procedure.</div><div class=3D"=
gmail_extra">

<br></div><div class=3D"gmail_extra">Anyway, I suppose PostgreSQL returns r=
esult set resource. Are you saying</div><div class=3D"gmail_extra">PDO pgsq=
l wouldn&#39;t? or missing columns? How about pgsql module? Does it work?</=
div>

<div class=3D"gmail_extra">I guess you have been tested, but the &quot;sele=
ct&quot; in procedure works as expected</div><div class=3D"gmail_extra">as =
simple query? If so, what&#39;s the expected output?<br><br>Regards,<br><br=
 clear=3D"all">

<div>--<br>Yasuo Ohgaki <br><a href=3D"mailto:[email protected]">yohgaki@o=
hgaki.net</a></div>
</div></div>

--001a11c3bb346c67e304ea4c8817--