Thread: RETURNS SETOF primitive returns results in parentheses

RETURNS SETOF primitive returns results in parentheses

From
Mario Splivalo
Date:
Consider this function:

CREATE OR REPLACE FUNCTION php_get_subfield_data_repeating(int4,
"varchar") RETURNS SETOF "varchar" AS
$BODY$
DECLAREaRecordID ALIAS FOR $1;aSubFieldId ALIAS FOR $2;
returnValue record;subFieldNumber char(3);subFieldLetter char(1);

BEGINsubFieldNumber = substr(aSubFieldId, 1, 3);subFieldLetter = substr(aSubFieldId, 4);
FOR returnValue IN SELECT "subfieldValue"::varchar            FROM "records_sub"             WHERE "fieldTag" =
subFieldNumberAND "subfieldTag" = subFieldLetter
 
AND "recordId" = aRecordIdLOOP    RETURN NEXT returnValue;END LOOP;
RETURN;
END
$BODY$ LANGUAGE 'plpgsql' VOLATILE;



Now, when I do this:

biblio3=# select * from php_get_subfield_data_repeating(1,'606a');php_get_subfield_data_repeating1
----------------------------------(Anđeli)(ofsajd)
(2 rows)

I have return values in parentheses. However, if I create a new type:

CREATE TYPE subfield_data_type AS (subfield_data varchar);

And then drop the function and recreate it like this:

CREATE OR REPLACE FUNCTION php_get_subfield_data_repeating(int4,
"varchar") RETURNS SETOF subfield_data_type AS
$BODY$
...

And then when I run the function, the results are ok:

biblio3=# select * from php_get_subfield_data_repeating(1,'606a');subfield_data
---------------Anđeliofsajd
(2 rows)


Am I doing something wrong here? Why do I need to create type with only
one member of type varchar to have results without the parentheses?
Mike

P.S. The subFieldValue field in the records_sub table is of type
varchar(4096).


-- 
Mario Splivalo
Mob-Art
mario.splivalo@mobart.hr

"I can do it quick, I can do it cheap, I can do it well. Pick any two."





Re: RETURNS SETOF primitive returns results in parentheses

From
Sean Davis
Date:
On 10/26/05 8:38 AM, "Mario Splivalo" <mario.splivalo@mobart.hr> wrote:

> Consider this function:
>
> CREATE OR REPLACE FUNCTION php_get_subfield_data_repeating(int4,
> "varchar")
> RETURNS SETOF "varchar" AS
> $BODY$
> DECLARE
> aRecordID ALIAS FOR $1;
> aSubFieldId ALIAS FOR $2;
>
> returnValue record;
> subFieldNumber char(3);
> subFieldLetter char(1);
>
> BEGIN
> subFieldNumber = substr(aSubFieldId, 1, 3);
> subFieldLetter = substr(aSubFieldId, 4);
>
> FOR returnValue IN SELECT "subfieldValue"::varchar
> FROM "records_sub"
> WHERE "fieldTag" = subFieldNumber AND "subfieldTag" = subFieldLetter
> AND "recordId" = aRecordId
> LOOP
> RETURN NEXT returnValue;
> END LOOP;
>
> RETURN;
> END
> $BODY$
> LANGUAGE 'plpgsql' VOLATILE;
>
>
>
> Now, when I do this:
>
> biblio3=# select * from php_get_subfield_data_repeating(1,'606a');
> php_get_subfield_data_repeating1
> ----------------------------------
> (Anđeli)
> (ofsajd)
> (2 rows)

Does:

select * from php_get_subfield_data_repeating(1,'606a') as s(a)

do what you want (single column)?



Re: RETURNS SETOF primitive returns results in parentheses

From
Mario Splivalo
Date:
On Wed, 2005-10-26 at 08:54 -0400, Sean Davis wrote:
> > Now, when I do this:
> > 
> > biblio3=# select * from php_get_subfield_data_repeating(1,'606a');
> > php_get_subfield_data_repeating1
> > ----------------------------------
> > (Anđeli)
> > (ofsajd)
> > (2 rows)
> 
> Does:
> 
> select * from php_get_subfield_data_repeating(1,'606a') as s(a)
> 
> do what you want (single column)?
> 

Nope. I still get the results in parentheses. When I change the SETOF
from varchar to my defined type, your query [with as s(a)] I get normal
resutls, withouth parentheses. I clearly have solved a problem, I just
need to create a type containing one member only, with the type of
varchar, and instead of 'RETURNS SETOF varchar' i need to do 'RETURNS
SETOF my_varchar_type'. I'm just wondering is this like that 'by
design', or is it a bug.

I've been reproducing this on postgres versions 7.4.8, 7.4.9, 8.0.3,
8.0.4 and 8.1beta3.
Mike
-- 
Mario Splivalo
Mob-Art
mario.splivalo@mobart.hr

"I can do it quick, I can do it cheap, I can do it well. Pick any two."




Re: RETURNS SETOF primitive returns results in parentheses

From
Tom Lane
Date:
Mario Splivalo <mario.splivalo@mobart.hr> writes:
> biblio3=# select * from php_get_subfield_data_repeating(1,'606a');
>  php_get_subfield_data_repeating1
> ----------------------------------
>  (Anđeli)
>  (ofsajd)
> (2 rows)

> I have return values in parentheses.

You're getting bit by plpgsql's perhaps-excessive willingness to convert
datatypes.  Your returnValue variable is not a varchar, it is a record
that happens to contain one varchar field.  When you do "RETURN NEXT
returnValue", plpgsql has to coerce that record value to varchar, and
it does that by converting the record value to text ... which produces
the parenthesized data format specified at 
http://www.postgresql.org/docs/8.0/static/rowtypes.html#AEN5604
        regards, tom lane


Re: RETURNS SETOF primitive returns results in parentheses

From
Stephan Szabo
Date:
On Wed, 26 Oct 2005, Mario Splivalo wrote:

> Consider this function:
>
> CREATE OR REPLACE FUNCTION php_get_subfield_data_repeating(int4,
> "varchar")
>   RETURNS SETOF "varchar" AS
> $BODY$
> DECLARE
>     aRecordID ALIAS FOR $1;
>     aSubFieldId ALIAS FOR $2;
>
>     returnValue record;
>     subFieldNumber char(3);
>     subFieldLetter char(1);
>
> BEGIN
>     subFieldNumber = substr(aSubFieldId, 1, 3);
>     subFieldLetter = substr(aSubFieldId, 4);
>
>     FOR returnValue IN SELECT "subfieldValue"::varchar
>                 FROM "records_sub"
>                 WHERE "fieldTag" = subFieldNumber AND "subfieldTag" = subFieldLetter
> AND "recordId" = aRecordId
>     LOOP
>         RETURN NEXT returnValue;

I think the root cause is that you're not returning a varchar here, but
instead a record containing a varchar (if I return next
returnValue."subfieldValue" I don't seem to get parens).  I'm not sure why
it's allowing you to do so, though, it seems like that shouldn't match the
return type.


Re: RETURNS SETOF primitive returns results in parentheses

From
Mario Splivalo
Date:
On Wed, 2005-10-26 at 10:40 -0400, Tom Lane wrote:
> Mario Splivalo <mario.splivalo@mobart.hr> writes:
> > biblio3=# select * from php_get_subfield_data_repeating(1,'606a');
> >  php_get_subfield_data_repeating1
> > ----------------------------------
> >  (Anđeli)
> >  (ofsajd)
> > (2 rows)
>
> > I have return values in parentheses.
>
> You're getting bit by plpgsql's perhaps-excessive willingness to convert
> datatypes.  Your returnValue variable is not a varchar, it is a record
> that happens to contain one varchar field.  When you do "RETURN NEXT
> returnValue", plpgsql has to coerce that record value to varchar, and
> it does that by converting the record value to text ... which produces
> the parenthesized data format specified at
> http://www.postgresql.org/docs/8.0/static/rowtypes.html#AEN5604

Wo-ha, makes perfect sense. So, I'd go by with declaring the rec as
varchar, instead as of a record. Wich is what I should do in the first
place.

Thnx for pointing that out. :)
Mike



Re: RETURNS SETOF primitive returns results in parentheses

From
Tom Lane
Date:
Mario Splivalo <mario.splivalo@mobart.hr> writes:
> Wo-ha, makes perfect sense. So, I'd go by with declaring the rec as
> varchar, instead as of a record. Wich is what I should do in the first
> place.

Or just return the correct field out of it.
RETURN NEXT returnValue.fieldname;

I think you may have to do it this way because the FOR loop wants a
record variable.
        regards, tom lane


Re: RETURNS SETOF primitive returns results in parentheses

From
Mario Splivalo
Date:
On Wed, 2005-10-26 at 15:45 -0400, Tom Lane wrote:
> Mario Splivalo <mario.splivalo@mobart.hr> writes:
> > Wo-ha, makes perfect sense. So, I'd go by with declaring the rec as
> > varchar, instead as of a record. Wich is what I should do in the first
> > place.
> 
> Or just return the correct field out of it.
> 
>     RETURN NEXT returnValue.fieldname;
> 
> I think you may have to do it this way because the FOR loop wants a
> record variable.

Yes, you're right. Funny implementation of the FOR loop :)
Mike