創建一個函式以從現有表中回傳一些資料。我根據運行時傳遞的函式引數動態地為選擇查詢構造 schema.table，但是在呼叫函式時它什么也不回傳。
下面的代碼
DROP TYPE IF EXISTS metadata.RETURNED_DATA CASCADE;
CREATE TYPE metadata.RETURNED_DATA AS
(
postal_id BIGINT,
postal_code VARCHAR(15),
admin_id BIGINT,
admin_code VARCHAR(11)
);
DROP FUNCTION IF EXISTS metadata.pc_get_data(VARCHAR, VARCHAR, VARCHAR);
CREATE OR REPLACE FUNCTION metadata.pc_get_data(reg CHARACTER VARYING, iso CHARACTER VARYING,
pcode CHARACTER VARYING
) RETURNS SETOF metadata.RETURNED_DATA
LANGUAGE plpgsql
AS
$$
BEGIN
EXECUTE FORMAT(
'SELECT postal_id,
postal_code,
admin_id,
admin_code
FROM %I_content.%I_pc_aet_data
WHERE postal_code = %L', reg, iso, pcode);
END
$$;
SELECT *
FROM pc_get_data('aaa', 'bbb', '12345');
uj5u.com熱心網友回復：
我不熟悉這種語法，現在沒有辦法測驗它，所以，如果我的回答有誤，請告訴我，我會編輯/洗掉它。
根據檔案，您可以執行以下操作select into：https ://www.postgresql.org/docs/9.1/plpgsql-statements.html
例子：
EXECUTE 'SELECT count(*) FROM mytable WHERE inserted_by = $1 AND inserted <= $2'
INTO c
USING checked_user, checked_date;
也許您可以執行以下操作（未經測驗）：
DROP FUNCTION IF EXISTS metadata.pc_get_data(VARCHAR, VARCHAR, VARCHAR);
CREATE OR REPLACE FUNCTION metadata.pc_get_data(reg CHARACTER VARYING, iso CHARACTER VARYING,
pcode CHARACTER VARYING
) RETURNS SETOF metadata.RETURNED_DATA
LANGUAGE plpgsql
AS
$$
BEGIN
EXECUTE FORMAT(
'SELECT postal_id,
postal_code,
admin_id,
admin_code
INTO TEMP TABLE my_temp
FROM %I_content.%I_pc_aet_data
WHERE postal_code = %L', reg, iso, pcode);
`SELECT * FROM my_temp;
END
$$;
uj5u.com熱心網友回復：
EXECUTE除非您使用SELECT...INTO.
好的例子可以在PostgreSQL wiki中找到
uj5u.com熱心網友回復：
嘗試使用text資料型別。
--https://wiki.postgresql.org/wiki/Don't_Do_This#Don.27t_use_varchar.28n.29_by_default
CREATE TYPE metadata.RETURNED_DATA AS
(
postal_id BIGINT,
postal_code text,
admin_id BIGINT,
admin_code text
);
CREATE OR REPLACE FUNCTION metadata.pc_get_data(reg text, iso text, pcode text
) RETURNS SETOF aaa_content.RETURNED_DATA
LANGUAGE plpgsql
AS
$$
BEGIN
return query execute FORMAT(
'SELECT postal_id,
postal_code,
admin_id,
admin_code
FROM %I_content.%I_pc_aet_data
WHERE postal_code = %L', reg, iso, pcode);
END
$$;
小心復合型別：整數型別的輸入語法無效：執行函式時復合資料型別的“(2,2)”
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/443422.html
標籤：PostgreSQL plpgsql 动态 sql
上一篇：使用包含重復元素的陣列選擇重復值