SQL query with variables in Postgresql

Question

I have an SQL query

SELECT c,d FROM tableX where a='str' AND b=var1 ;

I would like to substitute the var1 with a variable. I tried to use plpgsql.

CREATE OR REPLACE FUNCTION foo (var1 integer) 
RETURNS TABLE (c integer, d varchar) AS
$BODY$
DECLARE 
 aa varchar = 'str';
BEGIN 
RETURN QUERY EXECUTE 
'SELECT c,d FROM tableX where a=aa AND b=@1' using var1;
END;
$BODY$
LANGUAGE plpgsql;

The error is

No operator matches the given name and argument type(s). You might need to add explicit type casts.

Answer 1

First - the correct way to specify parameters is 1ドル, not @1.

Second - you do not need dynamic sql to pass parameters to the query. Just write something like:

CREATE OR REPLACE FUNCTION foo (var1 integer) 
RETURNS TABLE (c integer, d varchar) AS
$BODY$
DECLARE 
 aa varchar = 'str';
BEGIN 
 RETURN QUERY SELECT c,d FROM tableX where a=aa AND b=var1;
END;
$BODY$
LANGUAGE plpgsql;

• user330315: Using plpgsql is actually an overhead here. This could be done with a plain SQL function as well.
• Ihor Romanchenko: @a_horse_with_no_name I know. It is just an example.

Answer 2

Just to practice in PostgreSQL, as a_horse_with_no_name said, it's possible to write function in plain SQL, here's my attempt:

CREATE FUNCTION foo1 (var1 integer) RETURNS TABLE(c int, d text) 
 AS $$ SELECT c,d FROM tableX where a='str' AND b=1ドル $$
 LANGUAGE SQL;

SQL FIDDLE EXAMPLE

Answer 3

Just try:

CREATE OR REPLACE FUNCTION foo (var1 integer) 
 RETURNS TABLE (c integer, d varchar) AS
$BODY$
DECLARE 
 aa varchar = 'str';
BEGIN 
RETURN QUERY 
 SELECT c,d FROM tableX where a=aa AND b=var1;
END;