passing function parameters to dynamic query string

Question

I'm using PostGreSQL 11.4, just started to play with dynamic queries and I'm missing something. I want to use a function parameter inside the query and I'm doing it wrong.

this is what I have:

create or replace function media_query.my_test(p_from_timestamp timestamptz) RETURNS TABLE (moshe timestamptz) as
$aa$
BEGIN
    RETURN QUERY EXECUTE 'select p_from_timestamp as moshe';
END $aa$ LANGUAGE plpgsql;

when I execute select * from media_query.my_test(now()); I get the error column "p_from_timestamp" does not exist

so it's not a column it's a variable... how do I treat it as a variable ?

do I need to convert the variable to a string variable and contact it to the select? there is no way to the query string itself to parse variables somehow ?

thanks

score 1 · Accepted Answer · answered Aug 22 '19 at 12:31

It would be best to use it as a parameter, then you don't have to mess with escaping the value and you don't have to worry about SQL injection:

CREATE FUNCTION media_query.my_test(p_from_timestamp timestamptz)
   RETURNS TABLE (moshe timestamptz)
   IMMUTABLE AS
$aa$
BEGIN
    RETURN QUERY EXECUTE 'select $1' USING p_from_timestamp;
END;$aa$
LANGUAGE plpgsql;

If your goal is to play with constructing a query string from a literal, here goes:

CREATE FUNCTION media_query.my_test(p_from_timestamp timestamptz)
   RETURNS TABLE (moshe timestamptz)
   IMMUTABLE AS
$aa$
BEGIN
    RETURN QUERY EXECUTE format('select %L', p_from_timestamp);
END;$aa$
LANGUAGE plpgsql;

passing function parameters to dynamic query string

1 Answers1