CODEWITHSUNDEEP
sqlpostgresqldynamicplpgsql
Haris
Haris

Reputation: 77

Removing double quotes from concatenated string in plpgsql identifier

I have written the following function that returns records from another table 'parameters_ltree_{time_created}' where time_created is a column from historical_ltree table. 

CREATE OF REPLAE function get_my_path(date_string text, path_arg ltree) RETURNS SETOF  ltree AS
$BODY$
DECLARE
    p text;
    d text;
BEGIN
    d := quote_ident(date_string);
    p := 'parameters_ltree_';
    RETURN QUERY EXECUTE format(
    'SELECT  path from %I' || '%I
    WHERE path = %I
    ORDER BY path
    LIMIT 1'
    , p, date_string, path_arg);

END
$BODY$
LANGUAGE plpgsql;

SELECT id, path, get_my_path(to_char(time_created, 'YYYYMMDD')), path) from historical_ltree

The function compiles but when I run the SELECT query at the bottom it throws up a syntax error:

ERROR: relation "parameters_ltree_" does not exist
LINE 1: SELECT path FROM parameters_ltree_"20161201"
                         ^

I have tried many different methods to get that concatenation to work properly but to no avail including passing date_string as a numeric.

Upvotes: 3

Views: 1962

Answers (2)

Vao Tsun
Vao Tsun

Reputation: 51476

try changing to:

    RETURN QUERY EXECUTE format(
    'SELECT  path from %I
    WHERE path = %L
    ORDER BY path
    LIMIT 1'
    , 'parameters_ltree_'||date_string, path_arg);

https://www.postgresql.org/docs/current/static/functions-string.html

s formats the argument value as a simple string. A null value is treated as an empty string.

I treats the argument value as an SQL identifier, double-quoting it if necessary. It is an error for the value to be null (equivalent to quote_ident).

L quotes the argument value as an SQL literal. A null value is displayed as the string NULL, without quotes (equivalent to quote_nullable).

Upvotes: 3

Anuraag Veerapaneni
Anuraag Veerapaneni

Reputation: 679

You can try this

p := 'parameters_ltree_'||date_string;
    RETURN QUERY EXECUTE format(
    'SELECT  path from %I
    WHERE path = %I
    ORDER BY path
    LIMIT 1'
    , p, path_arg);

Upvotes: 0

Related Questions