Re: Isolating a record column from a PL-Pgsql function call ? - Mailing list pgsql-general

Wheeew! OK, that does work.
This knowlege creates new options for doing other stuff on my plate.

Thanks to all who responded !

-dave

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Sam Mason
Sent: Wednesday, December 17, 2008 6:45 AM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Isolating a record column from a PL-Pgsql function call ?

On Tue, Dec 16, 2008 at 01:03:05PM -0700, Gauthier, Dave wrote:
> The example I gave was overly simplistic.  I actually want to look at
> more than one of the columns in the returned record, so rewritting it
> to return an int won't address the need for the other columns.  And
> no, it does not return a set/array of records.  It returns just one
> record.
>
> If I run the function outside of a query, it returns something like...
>
> "(myvcharval1,myvcharval2,myintval)"
>
> Notice the parens, the commas and the lack of single quotes around the
> "myvcharval(x)" values.

This is how a value of type record is serialized.  When you're inside PG
it knows about the structure of the value and gives nice syntax to pull
the thing apart in (reasonably) nice ways.

To answer your question, if you have a function like:

  CREATE FUNCTION foo(p INT, OUT x INT, OUT y INT) RETURNS RECORD ...

Then I think you want to do something like:

  SELECT a, x, y
  FROM (
    SELECT t.a, (foo(t.b)).*
    FROM tbl t
    WHERE t.c = 10) z;

The reason you have to do this, and not have the function in the FROM
clause, is because each item in the FROM clause is independent.  There's
some standard syntax to say when this isn't true, but PG doesn't know
about it yet.

One other, recently reported[1], caveat is that PG currently evaluates
the function for each parameter returned from the SELECT statement it
appears in; so in the above example it'll get called twice.  It seems
possible to work around this by doing the following:

  SELECT a, (foo).x, (foo).y
  FROM (
    SELECT t.a, foo(t.b)
    FROM tbl t
    WHERE t.c = 10) z;

As Tom said in another response, the parens are a bit annoying but
needed to keep things un-ambiguous.  I think it's something to do with
schemas; for example the "(foo).x" above is really short for "z.foo.y".
This is then ambiguous whether you're referring to what's written
above, or referring to table "foo" in schema "z".  PG's fix for this
was to introduce the brackets and this, in combination with allowing
non-ambiguous column names to be referenced without a table name,
means we end up with the strange "(foo)" syntax.  At least that's my
understanding.


  Sam

 [1] http://archives.postgresql.org/pgsql-hackers/2008-12/msg00483.php

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