PostgreSQL: How to give integer value within 'select' statement?

Question

Here I need to check condition within the select statement using dblink. The following example having more details as shown below:

Example:

 create or replace function dblink_fun(ID bigint) returns void as
 $$
 Begin
 perform dblink_connect('hostaddr=127.0.0.0 port=5432 dbname=db user=postgres password=***');

 With x AS ( Select      "EmpID","EmpNo","Slno","Edate"
        From tabletest c 
        Where c."EmpID" = ID
    )
, y AS 
( Select Row_Number() over ( Partition by "Slno" order by  "DateOfJoinig" Desc) AS Rnk,
"SName","Slno","DateOfJoining","TimeOfJoining","Address",Ranked."empID"
  from dblink('select
             "Slno"',
    (   case when "FirstName" is null then '' 
        else "FirstName" end || '' ||
        Case When "MiddleName" is null then '' 
        else "LastName" end || '' ||
    ) AS name ,
    (   Case When "Address1" is null then '' 
        else "Address1" end || '' || 
        Case When "Address2" is null then ''
        else "Address2" end || '' ||
    ) AS address,
    "JoinigDate", "TimeofJoin", "EmpID" From "remote_table"
    inner join "x" on x."Slno" = "Slno"
    Where x."Edate" >= (
 Case When "DateOfJoinig" is null then '1900-01-01' else "DateOfJoinig" end' /*error near 1900 */
 ) Ranked
 )

 perform dblink_disconnect();
 end;
 $$
 Language plpgsql;

ERROR: syntax error at or near "1900"

Those ...s are important. Show the whole thing, please, or a self-contained example that has the problem where you've trimmed it down to show just the problem. — Craig Ringer
– Craig Ringer, Commented Jun 3, 2014 at 7:22

Bohemian · Accepted Answer · 2014-06-03 08:18:41Z

1

Your entire select statement is wrapped in single quotes:

from dblink('select ...

but you have single quoted values within the SQL itself.

You must escape the embedded single quotes by doubling them

Just replace every single quote within your select query to two single quotes.

Your exact error is caused because it so happens that 1900 falls outside an embedded single quote as coded.

answered Jun 3, 2014 at 8:18

Bohemian♦

427k103 gold badges603 silver badges750 bronze badges

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

Collectives™ on Stack Overflow

PostgreSQL: How to give integer value within 'select' statement?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related