在Postgresql中使用变量的SQL查询。

I have an SQL query

我有一个SQL查询。

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.

我想用变量替换var1。我尝试使用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.

3 个解决方案

#1

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

首先，指定参数的正确方法是$1，而不是@1。

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

第二，不需要动态sql将参数传递给查询。只写类似:

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;

#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:

仅仅是在PostgreSQL中练习，正如a_mah_with_no_name所说，可以用纯SQL编写函数，这是我的尝试:

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

SQL小提琴的例子

#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;

#1