Subject: RETURNS SETOF primitive returns results in parentheses
From: Mario Splivalo <mario.splivalo@mobart.hr>
Reply-To: mario.splivalo@mobart.hr
To: pgsql-sql@postgresql.org
Content-Type: text/plain; charset=UTF-8
Organization: Mob-Art
Date: Wed, 26 Oct 2005 14:38:42 +0200
Message-Id: <1130330322.7400.45.camel@ekim>
Mime-Version: 1.0
Content-Transfer-Encoding: 8bit

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.splivalo@mobart.hr

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