在 PostgreSQL 函式中引數化間隔日期型別的最佳方法是什么？我有以下功能
CREATE OR REPLACE FUNCTION testing(
min_time integer
)
RETURNS void AS
$BODY$
BEGIN
EXECUTE format('CREATE TABLE foo AS
SELECT
gid,
now() concat(%s, ' hours')::interval as x,
FROM foobar
limit 3 ',min_time );
END;
$BODY$
LANGUAGE plpgsql;
每次我嘗試執行該函式時，都會出現以下錯誤 ERROR: syntax error at or near "hours"
uj5u.com熱心網友回復：
單引號必須在字串中轉義：
CREATE OR REPLACE FUNCTION testing(min_time integer) RETURNS void
LANGUAGE plpgsql AS
$BODY$
BEGIN
EXECUTE format(
'CREATE TABLE foo AS
SELECT gid,
now() ''%s hours''::interval as x
FROM foobar
limit 3',
min_time
);
END;
$BODY$;
但實際上，您不需要動態 SQL，靜態 SQL 可能是更好的選擇：
CREATE OR REPLACE FUNCTION testing(min_time integer) RETURNS void
LANGUAGE plpgsql AS
$BODY$
BEGIN
CREATE TABLE foo (
gid bigint NOT NULL,
x timestamp with time zone NOT NULL
);
INSERT INTO foo
SELECT gid,
now() min_time * '1 hour'::interval
FROM foobar
limit 3;
END;
$BODY$;
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/381356.html
標籤：sql PostgreSQL的 postgresql-14
上一篇：沒有聚合的行到列