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

Laurenz Albe · Accepted Answer · 2019-08-22 12:31:41Z

1

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;

answered Aug 22, 2019 at 12:31

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

passing function parameters to dynamic query string

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related