pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedGetting the return type right for SETOF
3+ messages / 2 participants
[nested] [flat]
* Getting the return type right for SETOF
@ 2003-08-30 20:42 Dan Langille <dan@langille.org>
0 siblings, 2 replies; 3+ messages in thread
From: Dan Langille @ 2003-08-30 20:42 UTC (permalink / raw)
To: pgsql-sql
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
\d element_type
Composite type "public.element_type"
Column | Type
------------+---------
id | integer
name | text
type | text
status | text
iscategory | boolean
isport | boolean
--
Dan Langille : http://www.langille.org/
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Getting the return type right for SETOF
@ 2003-08-30 20:59 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: Dan Langille <dan@langille.org>
1 sibling, 0 replies; 3+ messages in thread
From: Stephan Szabo @ 2003-08-30 20:59 UTC (permalink / raw)
To: Dan Langille <dan@langille.org>; +Cc: pgsql-sql
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)
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Getting the return type right for SETOF
@ 2003-08-31 01:16 Dan Langille <dan@langille.org>
parent: Dan Langille <dan@langille.org>
1 sibling, 0 replies; 3+ messages in thread
From: Dan Langille @ 2003-08-31 01:16 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone.bigpanda.com>; +Cc: pgsql-sql
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/
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2003-08-31 01:16 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-08-30 20:42 Getting the return type right for SETOF Dan Langille <dan@langille.org>
2003-08-30 20:59 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-08-31 01:16 ` Dan Langille <dan@langille.org>
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