Thread: RETURNS SETOF primitive returns results in parentheses
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."
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)?
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."
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
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.
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
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
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