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;

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;