Error: a column definition list is required for functions in dblink using PostgreSQL 9.3

Question

I have the following function:

In which I am updating one database table by joining other database table by using the dblink().

I have installed:

create extension dblink;

The more details as shown below:

CREATE OR REPLACE FUNCTION Fun_test
(
    Table_Name varchar
) 
RETURNS void AS

$BODY$

DECLARE
    dynamic_statement varchar;

BEGIN
    perform dblink_connect('port=5234 dbname=testdb user=postgres password=****');


    dynamic_statement := 'With CTE AS 
    ( 
        Select HNumber,JoiningDate,Name,Address
        From '|| Table_Name ||'c 
    )
    , Test_A 
    AS 
    ( 
    Select Row_Number() over ( Partition by PNumber order by Date1 Desc,Date2 Desc) AS roNum,
    Name,PNumber,Date1,Address
    From dblink(
            ''Select distinct PNumber,
            (
                case when fname is null then '' else fname end || '' ||
                case when lname is null then '' else lname end 
            ) as FullName,
            Address,
            Date1,Date2
            From testdb_Table
            inner join CTE on CTE.HNumber = PNumber''
           )  Num 
    )
    Update CTE
    Set 
    Name = Test_A.FullName
    ,SubAddress_A = Test_A.Address
    ,Date1 = Test_A.Date1
    from CTE 
    left outer join Test_A on 
    CTE.HNumber= Test_A.PNumber 
    where roNum =1';

    RAISE INFO '%',dynamic_statement;

    EXECUTE dynamic_statement;

    perform dblink_disconnect();
END;

$BODY$

LANGUAGE PLPGSQL;

Calling Function:

select fun_test('test1');

Getting an error:

ERROR:  a column definition list is required for functions returning "record"
LINE 11:    From dblink
                 ^

You have to tell PostgreSQL what the columns the dblink query will return are. See the manual for dblink. — Craig Ringer, Jan 20 '15 at 12:25
@Craig Ringer, Thank you. I did that by using `as t(column_list)`. But now I am getting new error : `relation "CTE" does not exist` while updating. — MAK, Jan 20 '15 at 12:38
You can't update a CTE term (`WITH` expression) in PostgreSQL. CTE terms aren't treated as implicitly updateable views, they're materialized like temp tables. Anyway, that's a separate problem. — Craig Ringer, Jan 20 '15 at 12:42
Please post a separate new question for that. Feel free to link to this one for context. — Craig Ringer, Jan 20 '15 at 12:47

Craig Ringer · Accepted Answer · 2015-01-20T12:48:55.183

8

You have to tell PostgreSQL what the columns the dblink query will return are.

See the manual for dblink for details.

This is the same as for any function returning a runtime-determined record type. You can't query it without telling PostgreSQL what the column layout of the results will be.

You use a column specifier list, e.g.

SELECT * FROM my_function_returning_record() f(col1 text, col2 integer);

If you are on a current PostgreSQL version you may want to look at postgres_fdw as an alternative to dblink.

edited Jan 20 '15 at 12:48

answered Jan 20 '15 at 12:42

Craig Ringer

307,061
76
688
778

Wiil you please help me for this: http://stackoverflow.com/questions/28018991/split-given-string-and-prepare-case-statement – MAK Jan 26 '15 at 10:49

Error: a column definition list is required for functions in dblink using PostgreSQL 9.3

1 Answers1