[SQL] 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$ 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) 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đeli ofsajd (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 [EMAIL PROTECTED] I can do it quick, I can do it cheap, I can do it well. Pick any two. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] RETURNS SETOF primitive returns results in parentheses
On 10/26/05 8:38 AM, Mario Splivalo [EMAIL PROTECTED] 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)? ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] RETURNS SETOF primitive returns results in parentheses
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 [EMAIL PROTECTED] I can do it quick, I can do it cheap, I can do it well. Pick any two. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] RETURNS SETOF primitive returns results in parentheses
Mario Splivalo [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] RETURNS SETOF primitive returns results in parentheses
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. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] RETURNS SETOF primitive returns results in parentheses
On Wed, 2005-10-26 at 10:40 -0400, Tom Lane wrote: Mario Splivalo [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] RETURNS SETOF primitive returns results in parentheses
Mario Splivalo [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] RETURNS SETOF primitive returns results in parentheses
On Wed, 2005-10-26 at 15:45 -0400, Tom Lane wrote: Mario Splivalo [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
