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 "("

Richard Huxton · Accepted Answer · 2014-05-29 06:55:05Z

1

You've messed up your quotimg.

You can use dollar-quoting inside the fumction too.

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

answered May 29, 2014 at 6:55

Richard Huxton

23.6k5 gold badges43 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sarfaraz Makandar · Accepted Answer · 2014-05-29 05:05:36Z

0

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;

answered May 29, 2014 at 5:05

Sarfaraz Makandar

6,85319 gold badges67 silver badges86 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related