CODEWITHSUNDEEP
sqlpostgresqldynamicplpgsql
Divide by Zero
Divide by Zero

Reputation: 1474

Using PostgreSQL parameters with booleans in EXECUTE / USING

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.

Upvotes: 0

Views: 1228

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270463

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)

Upvotes: 1

Related Questions