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@mail.ru
