Using PostgreSQL parameters with booleans in EXECUTE / USING

Question

i want to use parameters for my dynamic queries. I have a statements like so:

RETURN QUERY EXECUTE 'SELECT * FROM boards AS b WHERE b.slug = $1 AND $2'
USING filter_slug, parent_id_query;

I get a ERROR: argument of AND must be type boolean, not type text

if i do it like this:

RETURN QUERY EXECUTE 'SELECT * FROM boards AS b WHERE b.slug = ''' || filter_slug || ''' AND ' || parent_id_query;

it works though.

I feel like i am missing something / not understanding something. Please help.

"x and y" means both x and y must render to some form of boolean expression -- they need to yield either a true or a false. b.slug = $1 is definitely either true or false, but $2 would only do this if it were a boolean. I get the idea parent_id_query is not a boolean... is it a query? If so, you can only pass values to parameters. Parameters don't just do string substitutions. — Hambone
– Hambone, Commented Feb 4, 2019 at 10:48
Ah okay. That was helpful. I think i could fix it by only replacing the filter_slug with a parameter and leaving parent_id_query as it was. Thanks. — Divide by Zero
– Divide by Zero, Commented Feb 4, 2019 at 10:53

Gordon Linoff · Accepted Answer · 2019-02-04 11:26:48Z

1

What you are missing is how parameters are used. Parameters are not macros that replace arbitrary text inside a SQL statement. Instead, they are literal values assigned to "variables" inside the code. These values are typically numbers, strings, or dates.

In particular, parameters cannot be used for:

identifiers (columns names and table names)
function names
operators
SQL keywords
general expressions

So, unfortunately, you have to construct that part of the query without a generic parameter (although you can have $2 = $3)

answered Feb 4, 2019 at 11:26

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Comment

Laurenz Albe Over a year ago

Or, to put it shorter, you can use a parameter almost anywhere in a SELECT, INSERT, UPDATE` or DELETE statement where you could use a constant (literal).

Collectives™ on Stack Overflow

Using PostgreSQL parameters with booleans in EXECUTE / USING

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related