Thread: Getting the return type right for SETOF

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         | integername       | texttype       | textstatus     | textiscategory | booleanisport
   | boolean
 



-- 
Dan Langille : http://www.langille.org/

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)

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/