在子查询中使用参数

发布于 2025-01-11 09:28:32 字数 781 浏览 0 评论 0原文

如何在带有“ ”的子查询中使用我的参数？（Postgres v.10）

create or replace function test(p_1 character varying, p_2 character varying)
returns table (id integer, total integer, fruit character varying)
LANGUAGE plpgsql
AS $$ 
begin
 return query
    Select * from dblink(
                        'host=myhost
                        user=myuser
                        password=mypw
                        dbname=mydb',
                        'select id,total,fruit from fruits 
                        where fruit in (p_1,p_2)') as x(id integer,total integer,fruit varchar);
end;
$$

如果我调用该函数

select * from test('apple','orange')

，我会收到此错误：列“p_1”和“p_2”不存在。.

而不是说 p_1 ，可以使用 $ 符号或其他东西来调用参数。我不知道这是否是真正的方法，但我在任何地方都找不到有关它的任何文档？

原文

How can i use my parameters inside a subquery with ' '? (Postgres v.10)

create or replace function test(p_1 character varying, p_2 character varying)
returns table (id integer, total integer, fruit character varying)
LANGUAGE plpgsql
AS $ 
begin
 return query
    Select * from dblink(
                        'host=myhost
                        user=myuser
                        password=mypw
                        dbname=mydb',
                        'select id,total,fruit from fruits 
                        where fruit in (p_1,p_2)') as x(id integer,total integer,fruit varchar);
end;
$

If i call the function

select * from test('apple','orange')

I get this ERROR: colum "p_1" and "p_2" does not exist..

Instead of saying p_1 it's possible to call parameters with $ signs or something. I don't know if that's the real approach but i can't find any documentation about it anywhere?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

娇柔作态 2025-01-18 09:28:32

您可以尝试这个（未测试）：

 return query
    Select * from dblink(
                        'host=myhost
                        user=myuser
                        password=mypw
                        dbname=mydb',
                        'select id,total,fruit from fruits 
                        where fruit in (' || p_1 || ',' || p_2 || ')') as x(id integer,total integer,fruit varchar);

You can try this (not tested) :

 return query
    Select * from dblink(
                        'host=myhost
                        user=myuser
                        password=mypw
                        dbname=mydb',
                        'select id,total,fruit from fruits 
                        where fruit in (' || p_1 || ',' || p_2 || ')') as x(id integer,total integer,fruit varchar);

回复收藏 0 原文

~没有更多了~