PostgreSQL: How to assign "SELECT" query to variable using dblink?

Question

Here I am trying to assign a "SELECT" query to a specific variable. The select query is retrieving rows from other database table using dblink. For example as shown below:

Example: My try

create or replace function fun(names varchar(60), id bigint) returns void as
$$
Declare
        sql varchar;
Begin
        sql := 'Select * from dblink'('conn','select * from tablex') /* Error occurred here */  
               'where name = ''' || names ||'''';

        raise info '%',sql;

        execute sql,'names varchar(60)',names=names;
end;
$$
Language plpgsql;

ERROR: syntax error at or near "("

score 1 · Accepted Answer · answered May 29 '14 at 06:55

1

You've messed up your quotimg.

You can use dollar-quoting inside the fumction too.

sql := $SQL$...$SQL$

answered May 29 '14 at 06:55

Richard Huxton

21,516
3
39
51

score 0 · Answer 2 · answered May 29 '14 at 05:05

Solution: Here is the solution

create or replace function fun(names varchar(60), id bigint) returns void as
$$
Declare
        sql varchar;
Begin
        sql := 'Select * from dblink(''conn','select * from tablex'') 

           where name = ''' || names ||'''';

    raise info '%',sql;

    execute sql,'names varchar(60)',names=names;
end;
$$
Language plpgsql;

PostgreSQL: How to assign "SELECT" query to variable using dblink?

2 Answers2