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 elementWHERE 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');
QUERYPLAN
----------------------------------------------------------------------
-----------------------------------------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/
