本文共 1642 字，大约阅读时间需要 5 分钟。
实验如下：
RETURNS TABLE 中的变量名和SQL文中的变量名同名时，执行时会出错：
pgsql=# create table sales(itemno integer,quantity integer,price numeric);CREATE TABLEpgsql=# insert into sales values (100,15,11.2),(101,22,12.3);INSERT 0 2pgsql=# CREATE FUNCTION extended_sales(p_itemno int)pgsql-# RETURNS TABLE(quantity int, total numeric) AS $$pgsql$# BEGINpgsql$# RETURN QUERY SELECT quantity, quantity * price FROM salespgsql$# WHERE itemno = p_itemno;pgsql$# END;pgsql$# $$ LANGUAGE plpgsql;CREATE FUNCTIONpgsql=#
pgsql=# select extended_sales(101);ERROR: column reference "quantity" is ambiguousLINE 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_itemnoCONTEXT: PL/pgSQL function "extended_sales" line 2 at RETURN QUERYpgsql=#
此时，可以如此操作：
pgsql$# RETURN QUERY SELECT sales.quantity, sales.quantity * sales.price FROM salespgsql$# WHERE itemno = p_itemno;pgsql$# END;pgsql$# $$ LANGUAGE plpgsql;CREATE FUNCTIONpgsql=# 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$# BEGINpgsql$# RETURN QUERY SELECT quantity AS tmp_quantity, quantity * price AS tmp_total pgsql$# FROM salespgsql$# WHERE itemno = p_itemno;pgsql$# END;pgsql$# $$ LANGUAGE plpgsql;CREATE FUNCTIONpgsql=# pgsql=# select extended_sales(101); extended_sales ---------------- (22,270.6)(1 row)pgsql=#
转载地址：http://nhzfo.baihongyu.com/