PostgreSQL Stored Procedure, How to Return Result Set

Question

I trying to create a stored procedure that will return a result set. I am using a dynamic query so it first created through a string then be executed in the end of the procedure. I just don't know how to return the whole result of the Select statment.

My query works because I tested it already (dont mind the select query it is just a sample). How do you do this properly? can someone help me with this?

here is my Stored Procedure:

  CREATE or REPLACE FUNCTION getTranscriptData(fromDate text, toDate text, idno integer = 0) 
RETURNS  TABLE(
    id integer,
    employee_id integer,
    employee_name text,
    client_id integer,
    client_name text
) AS -- text AS --
$body$
DECLARE
whereclause TEXT;
fullsql TEXT;
records RECORD;
exeQuery TEXT;

BEGIN

IF idno = 0 THEN
    whereclause := 'WHERE logs.timestamp  - INTERVAL ''12 hours''  >= ''' || fromDate || '''::timestamp ';
    whereclause := whereclause|| ' AND logs.timestamp  - INTERVAL ''12 hours'' <= ''' || toDate || '''::timestamp';
ELSE
    whereclause := ' WHERE trans.trans_id IN (1,2,3) ';
END IF;

--RAISE NOTICE 'Whereclause = "%"', whereclause;


fullsql:= 'SELECT
        trans.trans_id AS id, agent.account_id AS agent_id, agent.lastname || '', '' || agent.firstname AS agent_name, 
        client.account_id AS client_id, client.lastname || '', '' || client.firstname AS client_name
      FROM chat_transcript_archive trans
      INNER JOIN Client_session_archive csession ON csession.client_session_id = trans.client_session_id
      INNER JOIN client_queue_archive clientq ON clientq.Client_queue_id = trans.Client_queue_id
      INNER JOIN agent_session_archive asession ON asession.agent_session_id = trans.agent_session_id
      INNER JOIN agent_queue_archive agentq ON agentq.agent_queue_id = trans.agent_queue_id
      INNER JOIN accounts client ON client.account_id = csession.client_id
' || whereclause || '

      GROUP BY trans.trans_id, agent.account_id, agent.lastname, agent.firstname, client.account_id, client.lastname, client.firstname

      ORDER BY 13';

RAISE NOTICE 'FULL Query = "%"', fullsql;

exeQuery := 'SELECT * FROM (' || fullsql || ') AS records'

RETURN QUERY EXECUTE exeQuery;

END
$body$
LANGUAGE plpgsql;

Chris Travers · Accepted Answer · 2016-06-05 06:05:25Z

1

as you found out, you missed a semicolon.

However you are obviously making things too complex for yourself. Removing that whole line and just doing:

RETURN QUERY fullsql;

should do the trick more elegantly. You get nothing by turning it into an inline view just to give it an alias.

answered Jun 5, 2016 at 6:05

Chris Travers

26.6k6 gold badges70 silver badges191 bronze badges

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

Comments

oneofakind · Accepted Answer · 2013-10-07 08:43:00Z

0

I'm sorry guys. I was able to solve this. The problem was there is no semicolon at this line

 exeQuery := 'SELECT * FROM (' || fullsql || ') AS records'

I feel so stupid. Anyway, to help those have questions on stored procedure here you go.

answered Oct 7, 2013 at 8:43

oneofakind

56218 silver badges41 bronze badges

Collectives™ on Stack Overflow

PostgreSQL Stored Procedure, How to Return Result Set

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related