Thread: Impossible with pl/pgsql?

Hi,

I have a function find() that returns a SETOF INT. I further have a
function decorate as follows:

CREATE TYPE decorate_type AS (id INT, name TEXT, author TEXT,
last_change TIMESTAMP);
CREATE FUNCTION decorate(INT)RETURNS decorate_typeSTABLELANGUAGE 'plpgsql'AS '
-- BLACK BOX
';

Now I can do

SELECT decorate(4);
(4,egg,john,2003-05-05)

and I can do

SELECT * FROM decorate(4);
id | name | author | last_change
--------------------------------4 | egg  | john   | 2003-05-05

SELECT * FROM decorate(5);
id | name | author | last_change
--------------------------------5 | ham  | dave   | 2004-03-01

Let's say find() gives me 4 and 5:

SELECT * FROM find();
find
---- 4 5

Now how would a query look like that involves find() and decorate() and
returns

id | name | author | last_change
--------------------------------4 | egg  | john   | 2003-05-055 | ham  | dave   | 2004-03-01

I can't figure this out for the life of me.

I also have the impression that that's impossible to do without changing
find() or decorate().

Markus

-- 
Markus Bertheau ☭ <twanger@bluetwanger.de>

On Mon, May 30, 2005 at 05:15:55PM +0200, Markus Bertheau ??? wrote:
> 
> Now how would a query look like that involves find() and decorate() and
> returns
> 
> id | name | author | last_change
> --------------------------------
>  4 | egg  | john   | 2003-05-05
>  5 | ham  | dave   | 2004-03-01

Either of the following should work in PostgreSQL 8.0 and later:

SELECT (decorate(x)).* FROM find() AS f(x);
SELECT (decorate(find)).* FROM find();

A downside is that decorate() will be called once for each output
column in each row, as can be seen by adding debugging RAISE
statements.  So in your example it would be called eight times
(2 rows * 4 columns) instead of twice (once for each of 2 rows).

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Markus Bertheau ☭ <twanger@bluetwanger.de> writes:
> I have a function find() that returns a SETOF INT. I further have a
> function decorate as follows:

> CREATE TYPE decorate_type AS (id INT, name TEXT, author TEXT,
> last_change TIMESTAMP);
> CREATE FUNCTION decorate(INT)
>     RETURNS decorate_type

> Now how would a query look like that involves find() and decorate() and
> returns

> id | name | author | last_change
> --------------------------------
>  4 | egg  | john   | 2003-05-05
>  5 | ham  | dave   | 2004-03-01

One way isselect decorate(find) from find();
which will give you something like
                decorate
------------------------------------------(4,foo,bar,"2005-05-30 12:14:14.161292")(5,foo,bar,"2005-05-30
12:14:14.161292")
(2 rows)

If you want the columns of the rowtype broken apart, you can useselect (decorate(find)).* from find();
id | name | author |        last_change
----+------+--------+---------------------------- 4 | foo  | bar    | 2005-05-30 12:14:17.571481 5 | foo  | bar    |
2005-05-3012:14:17.571481
 
(2 rows)

although I believe this will result in multiple evaluations of decorate()
per row.  If decorate() is expensive you'll want to do something like
   select (decorate).* from (select decorate(find) from find() offset 0) ss;

where the OFFSET clause serves as an optimization fence to prevent the
planner from folding this down to the same as the previous version.

(This is all assuming PG 8.0 or later)
        regards, tom lane

Hi,
 I have tried it on PostgreSQL 8.0.3 and following query gives the result 
 SELECT ( DECORATE( FIND )).*  FROM FIND()

Regards,
R.Muralidharan  
   

-----Original Message-----
From: pgsql-sql-owner@postgresql.org
[mailto:pgsql-sql-owner@postgresql.org]On Behalf Of
twanger@bluetwanger.de
Sent: Monday, May 30, 2005 8:46 PM
To: 'pgsql-sql@postgresql.org'
Subject: [SQL] Impossible with pl/pgsql?


Hi,

I have a function find() that returns a SETOF INT. I further have a
function decorate as follows:

CREATE TYPE decorate_type AS (id INT, name TEXT, author TEXT,
last_change TIMESTAMP);
CREATE FUNCTION decorate(INT)RETURNS decorate_typeSTABLELANGUAGE 'plpgsql'AS '
-- BLACK BOX
';

Now I can do

SELECT decorate(4);
(4,egg,john,2003-05-05)

and I can do

SELECT * FROM decorate(4);
id | name | author | last_change
--------------------------------4 | egg  | john   | 2003-05-05

SELECT * FROM decorate(5);
id | name | author | last_change
--------------------------------5 | ham  | dave   | 2004-03-01

Let's say find() gives me 4 and 5:

SELECT * FROM find();
find
---- 4 5

Now how would a query look like that involves find() and decorate() and
returns

id | name | author | last_change
--------------------------------4 | egg  | john   | 2003-05-055 | ham  | dave   | 2004-03-01

I can't figure this out for the life of me.

I also have the impression that that's impossible to do without changing
find() or decorate().

Markus

-- 
Markus Bertheau ☭ <twanger@bluetwanger.de>


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend