Hello,

Suppose we have sql function which returns composite type.
When such function's last select statement doesn't return any row
error occurs -
ERROR: function returning row cannot return null value.
But if we use similar function that returns set of same type
error not occurs.

Example:

--------------------------------------------------------
CREATE TYPE "test_type" AS (
"id" integer,
"name" character varying(64),
"description" text
);

CREATE FUNCTION "test"(character varying) RETURNS "test_type"
AS '
select "id", "name", "description"
from "test"
where "name" = $1;
'
LANGUAGE sql SECURITY DEFINER;

CREATE FUNCTION "test2"(character varying) RETURNS SETOF "test_type"
AS '
select "id", "name", "description"
from "test"
where "name" = $1;
'
LANGUAGE sql SECURITY DEFINER;


CREATE TABLE "test" (
"id" serial NOT NULL,
"name" character varying(64) NOT NULL,
"description" text,
"update_time" timestamp without time zone DEFAULT now() NOT NULL
);

INSERT INTO "test" ("id", "name", "description", "update_time") VALUES (1, 'first', 'first row', '2004-09-21 15:32:41.171');
INSERT INTO "test" ("id", "name", "description", "update_time") VALUES (2, 'second', 'second row', '2004-09-21 15:32:54.64');
INSERT INTO "test" ("id", "name", "description", "update_time") VALUES (3, 'third', 'third row', '2004-09-21 15:33:08.406');

ALTER TABLE ONLY "test"
ADD CONSTRAINT "pk_test_id" PRIMARY KEY ("id");

ALTER TABLE ONLY "test"
ADD CONSTRAINT "unq_test_name" UNIQUE ("name");

--------------------------------------------------------
select * from "test"('second')
will return one row with data
but
select * from "test"('secon')
will raise an ERROR
and
select * from "test2"('secon')
will return empty set.

I think that is more conveniently that when no data is fetched
such function returns instance of composite type with nulls.

In case of function returning record type we also "know" schema of
the last select.

Of course it is possible to use plpgsql function and select into
but sql functions is smaller and don't require handler.

--
Best regards,
Ivan mailto:Ivan-Sun1 (AT) mail (DOT) ru


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Ivan <Ivan-Sun1 (AT) mail (DOT) ru> writes:
Works for me (tested in 7.4.5 and CVS tip).

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Ivan <Ivan-Sun1 (AT) mail (DOT) ru> writes:
Ah. I was trying "select test('foo')" which does work. Will look at
it.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Ivan <Ivan-Sun1 (AT) mail (DOT) ru> writes:
I've applied a patch to fix this for 8.0.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org