Hello Tom,
Tuesday, September 21, 2004, 6:17:37 PM, you wrote:
TL> Ivan <[EMAIL PROTECTED]> writes:
>> 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.
TL> Works for me (tested in 7.4.5 and CVS tip).
I've just (an hour ago) get latest version from CVS.
"PostgreSQL 8.0.0beta2 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.2.3 (mingw
special 20030504-1)"
Still have the same error - ERROR: function returning row cannot return null value
I attached sql file.
Error occurs when do
select * from "test"('secon')
--
Best regards,
Ivan mailto:[EMAIL PROTECTED]SET client_encoding = 'UNICODE';
SET check_functions_bodies = false;
SET search_path = public, pg_catalog;
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;
SET default_with_oids = true;
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
);
SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence('test', 'id'), 1, false);
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");
REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM postgres;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO PUBLIC;
---------------------------(end of broadcast)---------------------------
TIP 3: 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
