PL/pgSQL RETURNS TABLE 例子
实验如下：
RETURNS TABLE 中的变量名和SQL文中的变量名同名时，执行时会出错：
pgsql=# create table sales(itemno integer,quantity integer,price numeric); CREATE TABLE pgsql=# insert into sales values (100,15,11.2),(101,22,12.3); INSERT 0 2 pgsql=# CREATE FUNCTION extended_sales(p_itemno int) pgsql-# RETURNS TABLE(quantity int, total numeric) AS $$ pgsql$# BEGIN pgsql$# RETURN QUERY SELECT quantity, quantity * price FROM sales pgsql$# WHERE itemno = p_itemno; pgsql$# END; pgsql$# $$ LANGUAGE plpgsql; CREATE FUNCTION pgsql=#
pgsql=# select extended_sales(101); ERROR: column reference "quantity" is ambiguous LINE 1: SELECT quantity, quantity * price FROM sales ^ DETAIL: It could refer to either a PL/pgSQL variable or a table column. QUERY: SELECT quantity, quantity * price FROM sales WHERE itemno = p_itemno CONTEXT: PL/pgSQL function "extended_sales" line 2 at RETURN QUERY pgsql=#
此时，可以如此操作：
pgsql$# RETURN QUERY SELECT sales.quantity, sales.quantity * sales.price FROM sales pgsql$# WHERE itemno = p_itemno; pgsql$# END; pgsql$# $$ LANGUAGE plpgsql; CREATE FUNCTION pgsql=# pgsql=# select extended_sales(101); extended_sales ---------------- (22,270.6) (1 row) pgsql=#
也可以采用别的名称：
pgsql=# CREATE FUNCTION extended_sales(p_itemno int) pgsql-# RETURNS TABLE(tmp_quantity int, tmp_total numeric) AS $$ pgsql$# BEGIN pgsql$# RETURN QUERY SELECT quantity AS tmp_quantity, quantity * price AS tmp_total pgsql$# FROM sales pgsql$# WHERE itemno = p_itemno; pgsql$# END; pgsql$# $$ LANGUAGE plpgsql; CREATE FUNCTION pgsql=# pgsql=# select extended_sales(101); extended_sales ---------------- (22,270.6) (1 row) pgsql=#