Re: (SETOF) RECORD AS complex_type

From: Hannu Krosing <hannu(at)skype(dot)net>
To: David Fetter <david(at)fetter(dot)org>
Cc: PG Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: (SETOF) RECORD AS complex_type
Date: 2007-01-08 15:25:17
Message-ID: 1168269917.5756.3.camel@localhost.localdomain
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Ühel kenal päeval, K, 2006-12-27 kell 14:06, kirjutas David Fetter:
> Folks,
>
> While using DBI-Link, I've noticed a little lacuna in how functions
> returning (SETOF) RECORD work, namely, that you have to cast them to
> explicit lists of columns, even when that list of columns corresponds
> to an existing complex type.
>
> What would be involved in fixing the casting operation so that the
> following would work?
>
> CREATE TYPE foo AS (
> a INT4,
> b INT8,
> c POINT,
> d TEXT
> );
>
> CREATE FUNCTION bar(output_type TEXT)
> RETURNS SETOF RECORD
> ...
>
> SELECT * FROM bar('foo') AS foo;
>
> Cheers,
> D

using OUT parameters works nice for me

hannu=# CREATE FUNCTION bar(IN cnt INT, OUT a INT4, OUT b INT8, OUT c
POINT, OUT d TEXT)
hannu-# RETURNS SETOF RECORD
hannu-# LANGUAGE SQL
hannu-# AS $$
hannu$# SELECT '1'::INT4,'1'::INT8,'(1,1)'::POINT,'text'::TEXT FROM
generate_series(1,3);
hannu$# $$;
CREATE FUNCTION
hannu=# select * from bar(1);
a | b | c | d
---+---+-------+------
1 | 1 | (1,1) | text
1 | 1 | (1,1) | text
1 | 1 | (1,1) | text
(3 rows)

--
----------------
Hannu Krosing
Database Architect
Skype Technologies OÜ
Akadeemia tee 21 F, Tallinn, 12618, Estonia

Skype me: callto:hkrosing
Get Skype for free: http://www.skype.com

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2007-01-08 15:27:15 Re: BUG #2873: Function that returns an empty set with a 'not null' domain errors in 8.2 but not 8.1
Previous Message Dave Page 2007-01-08 11:46:24 Re: -f <output file> option for pg_dumpall