From: Dan Langille <dan@langille.org>
To: Stephan Szabo <sszabo@megazone.bigpanda.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Getting the return type right for SETOF
Date: Sat, 30 Aug 2003 21:16:26 -0400
Message-ID: <3F51142A.19665.30EA4C00@localhost> (raw)
In-Reply-To: <20030830135853.I74114-100000@megazone.bigpanda.com>
References: <3F50D3EF.27516.2FEF6552@localhost>
On 30 Aug 2003 at 13:59, Stephan Szabo wrote:
> On Sat, 30 Aug 2003, Dan Langille wrote:
>
> > Hi folks,
> >
> > I'm playing with SETOF on functions. But I can't get the return type
> > correct. What have I missed? A cast?
> >
> > CREATE OR REPLACE FUNCTION elementGet (text) RETURNS SETOF
> > element_type AS '
> >
> > select 1,
> > \'test\',
> > \'F\'
> > \'A\',
> > FALSE,
> > FALSE
> > '
> > LANGUAGE sql stable;
> > ERROR: function declared to return element_type returns "unknown"
> > instead of text at column 2
>
> I think you'll need to explicitly make the three text columns text rather
> than just a plain literal (so ''test''::text for example)
Right you are! Here is the real function:
CREATE OR REPLACE FUNCTION elementGet (text) RETURNS SETOF
element_type AS '
select id,
name::text,
directory_file_flag::text,
status::text,
case when IsPort( Pathname_ID($1)) IS NULL THEN FALSE ELSE
TRUE END,
case when IsCategory(Pathname_ID($1)) IS NULL THEN FALSE ELSE
TRUE END
FROM element
WHERE id = PathName_ID($1);
'
LANGUAGE sql stable;
select * from elementGet('ports/security/logcheck');
id | name | type | status | iscategory | isport
-------+----------+------+--------+------------+--------
37342 | logcheck | D | A | t | f
And it's fast too:
explain analyse select * from elementGet('ports/security/logcheck');
QUERY PLAN
----------------------------------------------------------------------
-----------------------------------------
Function Scan on elementget (cost=0.00..12.50 rows=1000 width=102)
(actual time=64.28..64.28 rows=1 loops=1)
Total runtime: 64.35 msec
Thank you.
--
Dan Langille : http://www.langille.org/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dan@langille.org, sszabo@megazone.bigpanda.com
Subject: Re: Getting the return type right for SETOF
In-Reply-To: <3F51142A.19665.30EA4C00@localhost>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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