Answer the question
In order to leave comments, you need to log in
Where is the error in EXECUTE FORMAT?
There is a table with data and a function:
CREATE TABLE IF NOT EXISTS in_table(
in_key INTEGER NOT NULL,
in_tst TIMESTAMPTZ NOT NULL,
in_val FLOAT NOT NULL);
INSERT INTO in_table (in_key, in_tst, in_val)
SELECT
(random()*30)::INT, tstamp, random()*80 - 40
FROM
generate_series(
NOW() - INTERVAL '90 days',
NOW(),
'1 min'
) AS tstamp;
CREATE OR REPLACE FUNCTION _Foo(st TIMESTAMP, fin TIMESTAMP)
RETURNS TABLE (out_key INTEGER, out_tst timestamptz, out_val FLOAT) AS $$
DECLARE
BEGIN
RETURN QUERY SELECT in_key, in_tst, in_val
FROM in_table
WHERE in_tst BETWEEN st AND fin;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION _Foo(st TIMESTAMP, fin TIMESTAMP)
RETURNS TABLE (out_key INTEGER, out_tst timestamptz, out_val FLOAT) AS $$
DECLARE
BEGIN
EXECUTE FORMAT('
RETURN QUERY SELECT %I, %I, %I
FROM %I
WHERE %I BETWEEN %I AND %I;
', in_key, in_tst, in_val, in_table, in_tst, st, fin);
END;
$$ LANGUAGE plpgsql;
Answer the question
In order to leave comments, you need to log in
CREATE OR REPLACE FUNCTION _Foo2(st TIMESTAMP, fin TIMESTAMP)
RETURNS TABLE (out_key INTEGER, out_tst timestamptz, out_val FLOAT) AS $$
DECLARE
BEGIN
RETURN QUERY EXECUTE FORMAT('
SELECT %I, %I, %I
FROM %I
WHERE %I BETWEEN ''%s'' AND ''%s'';
',
'in_key', 'in_tst', 'in_val', 'in_table', 'in_tst', st, fin);
END;
$$ LANGUAGE plpgsql;
Didn't find what you were looking for?
Ask your questionAsk a Question
731 491 924 answers to any question