In this post i am asking 2 questions (with my limited knowledge of PostgreSQL and PLPGSQL)
- I have a function inside which there is a query that inserts records to a table using INSERT INTO SELECT FROM. This query is failing the whole function when there is no record returned from the SELECT. Just like SQL Server I don’t want this query to fail, even if there is no record, I want the execution to be continued. What should I do to achieve such functionality.
- I have a refcursor to be returned as output however along with it i have few more params (2 or 3) to be returned as output such as bigint or char type. This refcursor would be getting data from a dynamic SQL which is getting formed inside function. So is my function inputs and outputs are in order and whether this will work?
CREATE OR REPLACE FUNCTION public.xyz(input1 INT, out1 OUT BIGINT, P_refcursor out REFCURSOR) RETURNS RECORD AS $body$ BEGIN <some code> /*This step fails the whole function and nothing executes after that*/ INSERT INTO tbl SELECT * FROM <joins> WEHRE <condition>; <some code> SELECT aaa INTO out1 FROM <somefunction> ; /*refcursor to be returned to the calling mechanism*/ open p_refcursor for EXECUTE sql_stmt; exception when others then Raise Notice '% - % ', SQLSTATE, SQLERRM; END; $body$ Language PLPGSQL VOLATILE;
EDIT-1: Below could be answer to Q2... Answer to Q1 is still pending...
I figured out that there could be different options to handle situation mentioned in Q2.
- Write these singular (non-cursor) params into some table and then access from outside the function to know about last execution value
- Include these other params as one of the column in REFCURSOR and get their values in the output when you access it
- What i did: Removed all the out params, converted the RETURN type of function as SETOF RECORD (my output was dynamic hence couldn't convert it to TABLE) and included the other singular out params (non-cursor) in the RECORD when forming my dynamic query. I was then able to access these out params when I called the function with field list (as my Return Type was SETOF RECORD). The downside of this would be these out param values would be repeated for all rows of the output and you wont have them when dynamic query returns no rows.