pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedImpossible with pl/pgsql?
4+ messages / 4 participants
[nested] [flat]
* Impossible with pl/pgsql?
@ 2005-05-30 15:15 Markus Bertheau ☭ <twanger@bluetwanger.de>
2005-05-30 16:13 ` Re: Impossible with pl/pgsql? Michael Fuhr <mike@fuhr.org>
2005-05-30 16:21 ` Re: Impossible with pl/pgsql? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 4+ messages in thread
From: Markus Bertheau ☭ @ 2005-05-30 15:15 UTC (permalink / raw)
To: pgsql-sql
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_type
STABLE
LANGUAGE '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-05
5 | 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>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Impossible with pl/pgsql?
2005-05-30 15:15 Impossible with pl/pgsql? Markus Bertheau ☭ <twanger@bluetwanger.de>
@ 2005-05-30 16:13 ` Michael Fuhr <mike@fuhr.org>
1 sibling, 0 replies; 4+ messages in thread
From: Michael Fuhr @ 2005-05-30 16:13 UTC (permalink / raw)
To: Markus Bertheau ??? <twanger@bluetwanger.de>; +Cc: pgsql-sql
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/
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Impossible with pl/pgsql?
2005-05-30 15:15 Impossible with pl/pgsql? Markus Bertheau ☭ <twanger@bluetwanger.de>
@ 2005-05-30 16:21 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 4+ messages in thread
From: Tom Lane @ 2005-05-30 16:21 UTC (permalink / raw)
To: Markus Bertheau ☭ <twanger@bluetwanger.de>; +Cc: pgsql-sql
Markus Bertheau =?UTF-8?Q?=E2=98=AD?= <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 is
select 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 use
select (decorate(find)).* from find();
id | name | author | last_change
----+------+--------+----------------------------
4 | foo | bar | 2005-05-30 12:14:17.571481
5 | foo | bar | 2005-05-30 12: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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Impossible with pl/pgsql?
@ 2005-05-31 04:40 Ramakrishnan Muralidharan <ramakrishnanm@pervasive-postgres.com>
0 siblings, 0 replies; 4+ messages in thread
From: Ramakrishnan Muralidharan @ 2005-05-31 04:40 UTC (permalink / raw)
To: Markus Bertheau ? <twanger@bluetwanger.de>; pgsql-sql
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_type
STABLE
LANGUAGE '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-05
5 | 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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2005-05-31 04:40 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-05-30 15:15 Impossible with pl/pgsql? Markus Bertheau ☭ <twanger@bluetwanger.de>
2005-05-30 16:13 ` Michael Fuhr <mike@fuhr.org>
2005-05-30 16:21 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-05-31 04:40 Re: Impossible with pl/pgsql? Ramakrishnan Muralidharan <ramakrishnanm@pervasive-postgres.com>
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