Postgres function with dblink

Question

Got a problem here with following function in postgresql function:

im getting following error :

ERROR: query has no destination for result data HINT: If you want to discard the results of a SELECT, use PERFORM instead. CONTEXT: PL/pgSQL function savegamelog(text,text,integer,integer,bigint,bigint,bigint,integer,integer,integer,integer) line 15 at SQL statement

my function is as follows:

    DECLARE
    s text;
    pDatatime integer;
    pDataid bigint;
BEGIN
pDatatime = floor(extract(epoch from '["now()",]'::timestamp));

IF length(pTableName) > 0 THEN
    UPDATE  lastgamedata  SET data= pData::bytea, gameid= pGameid, balance= pBalance ,bet= pBet, win= pWin, betline= pBetline, lines=pLines, datatime= pDatatime WHERE uid= pUserid;
END IF;

UPDATE gamestatistic SET totalin =totalin+pBet , totalout =totalout+pWin  WHERE gameindex=pGameid;

SELECT dblink_connect('host=127.0.0.1
user=user
password=pass
dbname=dbname');

SELECT dblink_exec('UPDATE hall SET totalbetin = totalbetin+pBet , totalbetout =totalbetout+pWin WHERE id = (SELECT roomnumber FROM users WHERE uid = pUserid)');

INSERT INTO gamedata( sessionID, uid, gameID, key, balance, bet, win, betline, lines, datatime, type, denomination ) VALUES ( 0, pUserid, pGameid, 0, pBalance, pBet, pWin, pBetline, pLines, pDatatime, pType, pDenomination ) RETURNING dataid INTO pDataid;

INSERT INTO gamedata_storage ( dataid, data ) VALUES ( pDataid, pData::bytea );

return pDataid;
END;

i know the function is wrong, and i dont know how to fix it.. can anybody point me in the correct direction pls..

change SELECT dblink_connect(... to perform dblink_connect(... as error message says — Vao Tsun
– Vao Tsun, Commented Aug 1, 2017 at 17:03
Just FYI the modern replacement for dblink is FDW. postgresql.org/docs/current/static/postgres-fdw.html — Scott Marlowe
– Scott Marlowe, Commented Aug 1, 2017 at 20:13
@ScottMarlowe there are uses for dblink other than connecting to remote databases, the main one being autonomous transactions where you would be connecting dblink to the same db you're already connected to — Anentropic
– Anentropic, Commented Sep 14, 2018 at 15:29

Vao Tsun · Accepted Answer · 2017-08-01 17:56:30Z

2

following the error message instructions, change

SELECT dblink_connect('host=127.0.0.1
user=user
password=pass
dbname=dbname');

to

PERFORM dblink_connect('host=127.0.0.1
user=user
password=pass
dbname=dbname');

answered Aug 1, 2017 at 17:56

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

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

Collectives™ on Stack Overflow

Postgres function with dblink

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