RETURNS SETOF primitive returns results in parentheses

From: Mario Splivalo <mario(dot)splivalo(at)mobart(dot)hr>
To: pgsql-sql(at)postgresql(dot)org
Subject: RETURNS SETOF primitive returns results in parentheses
Date: 2005-10-26 12:38:42
Message-ID: 1130330322.7400.45.camel@ekim
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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
mario(dot)splivalo(at)mobart(dot)hr

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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Sean Davis 2005-10-26 12:54:51 Re: RETURNS SETOF primitive returns results in parentheses
Previous Message Sean Davis 2005-10-26 10:58:16 Re: SETOF RECORD RETURN VALUE