我需要一个Postgres函数来返回一个带有自定义内容的虚拟表(就像在Oracle中一样)。该表将有3列和未知数量的行。
我只是在互联网上找不到正确的语法。
想象一下:
CREATE OR REPLACE FUNCTION "public"."storeopeninghours_tostring" (numeric)
RETURNS setof record AS
DECLARE
open_id ALIAS FOR $1;
returnrecords setof record;
BEGIN
insert into returnrecords('1', '2', '3');
insert into returnrecords('3', '4', '5');
insert into returnrecords('3', '4', '5');
RETURN returnrecords;
END;这是如何正确编写的呢?
发布于 2009-06-05 10:50:06
(这些都是用postgresql 8.3.7测试的--你有更早的版本吗?看看你对“$1的别名”的使用)
CREATE OR REPLACE FUNCTION storeopeninghours_tostring(numeric)
RETURNS SETOF RECORD AS $$
DECLARE
open_id ALIAS FOR $1;
result RECORD;
BEGIN
RETURN QUERY SELECT '1', '2', '3';
RETURN QUERY SELECT '3', '4', '5';
RETURN QUERY SELECT '3', '4', '5';
END
$$;如果要返回记录或行变量(而不是查询结果),请使用"RETURN NEXT“而不是"RETURN QUERY”。
要调用该函数,您需要执行如下操作:
select * from storeopeninghours_tostring(1) f(a text, b text, c text);因此,您必须定义在查询中期望函数的输出行模式是什么。为了避免这种情况,您可以在函数定义中指定输出变量:
CREATE OR REPLACE FUNCTION storeopeninghours_tostring(open_id numeric, a OUT text, b OUT text, c OUT text)
RETURNS SETOF RECORD LANGUAGE 'plpgsql' STABLE STRICT AS $$
BEGIN
RETURN QUERY SELECT '1'::text, '2'::text, '3'::text;
RETURN QUERY SELECT '3'::text, '4'::text, '5'::text;
RETURN QUERY SELECT '3'::text, '4'::text, '5'::text;
END
$$;(不太确定为什么需要额外的::文本类型转换...“1”在默认情况下可能是varchar?)
发布于 2009-06-05 11:46:05
我在函数中大量使用临时表。您需要在数据库上创建一个返回类型,然后创建要返回的该类型的变量。下面的示例代码就是这样做的。
CREATE TYPE storeopeninghours_tostring_rs AS
(colone text,
coltwo text,
colthree text
);
CREATE OR REPLACE FUNCTION "public"."storeopeninghours_tostring" () RETURNS setof storeopeninghours_tostring_rs AS
$BODY$
DECLARE
returnrec storeopeninghours_tostring_rs;
BEGIN
BEGIN
CREATE TEMPORARY TABLE tmpopeninghours (
colone text,
coltwo text,
colthree text
);
EXCEPTION WHEN OTHERS THEN
TRUNCATE TABLE tmpopeninghours; -- TRUNCATE if the table already exists within the session.
END;
insert into tmpopeninghours VALUES ('1', '2', '3');
insert into tmpopeninghours VALUES ('3', '4', '5');
insert into tmpopeninghours VALUES ('3', '4', '5');
FOR returnrec IN SELECT * FROM tmpopeninghours LOOP
RETURN NEXT returnrec;
END LOOP;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
select * from storeopeninghours_tostring()发布于 2009-06-05 10:59:28
CREATE OR REPLACE FUNCTION foo(open_id numeric, OUT p1 varchar, OUT p2 varchar, OUT p3 varchar) RETURNS SETOF RECORD AS $$
BEGIN
p1 := '1'; p2 := '2'; p3 := '3';
RETURN NEXT;
p1 := '3'; p2 := '4'; p3 := '5';
RETURN NEXT;
p1 := '3'; p2 := '4'; p3 := '5';
RETURN NEXT;
RETURN;
END;
$$ LANGUAGE plpgsql;https://stackoverflow.com/questions/955167
复制相似问题