Postgres Pro
Downloads
Home   >   mailing lists

Thread: recurse in a single query

recurse in a single query

From
"Jean-Yves F. Barbier"
Date:
Hi list,

I'd like to know how to recurse on some elements into a single query

SELECT P.oid, P.proname, P.proowner, P.pronargs, P.proargtypes::int[],
T.typname AS "PROCRETURN"  FROM pg_proc P INNER JOIN pg_type T ON
P.prorettype = T.oid WHERE pronamespace = 230741;

  oid   | proname | proowner | pronargs |    proargtypes    | PROCRETURN
--------+---------+----------+----------+-------------------+------------
 230749 | whoami  |   230738 |        0 | {}                | oid
 237125 | jyadd   |   230738 |        2 | [0:1]={1700,1700} | numeric

The goal is to get {numeric,numeric} instead of {1700,1700} still with one
query (if possible?)

JY
--
You've got to pity New Mexico... so far from heaven and so close to Texas.

Re: recurse in a single query

From
Thomas Kellerer
Date:
Jean-Yves F. Barbier, 06.10.2010 21:25:
> Hi list,
>
> I'd like to know how to recurse on some elements into a single query
>
> SELECT P.oid, P.proname, P.proowner, P.pronargs, P.proargtypes::int[],
> T.typname AS "PROCRETURN"  FROM pg_proc P INNER JOIN pg_type T ON
> P.prorettype = T.oid WHERE pronamespace = 230741;
>
>    oid   | proname | proowner | pronargs |    proargtypes    | PROCRETURN
> --------+---------+----------+----------+-------------------+------------
>   230749 | whoami  |   230738 |        0 | {}                | oid
>   237125 | jyadd   |   230738 |        2 | [0:1]={1700,1700} | numeric
>
> The goal is to get {numeric,numeric} instead of {1700,1700} still with one
> query (if possible?)
>

SELECT pg_get_function_arguments(p.oid)
FROM pg_proc p

Thomas

Re: recurse in a single query

From
"Jean-Yves F. Barbier"
Date:
On Wed, 06 Oct 2010 21:42:12 +0200, Thomas Kellerer <spam_eater@gmx.net>
wrote:

Cool, thanks Tom (I did not knew about these functions info:)

> SELECT pg_get_function_arguments(p.oid)
> FROM pg_proc p
>
> Thomas
>
>
>


--
My cup hath runneth'd over with love.

Re: recurse in a single query

From
Thomas Kellerer
Date:
Jean-Yves F. Barbier, 06.10.2010 21:56:
> Cool, thanks Tom (I did not knew about these functions info:)

They are all listed here:

http://www.postgresql.org/docs/current/static/functions-info.html

Thomas

Re: recurse in a single query

From
"Jean-Yves F. Barbier"
Date:
On Wed, 06 Oct 2010 22:05:51 +0200, Thomas Kellerer <spam_eater@gmx.net>
wrote:

Yep, found it and almost finished to read the whole page :)

> Jean-Yves F. Barbier, 06.10.2010 21:56:
> > Cool, thanks Tom (I did not knew about these functions info:)
>
> They are all listed here:
>
> http://www.postgresql.org/docs/current/static/functions-info.html
>
> Thomas
>
>


--
Lysistrata had a good idea.