PostgreSQL:- How to continue execution even INSERT INTO SELECT FROM fails and how to work with refcursor as out param with other output params

Question

In this post i am asking 2 questions (with my limited knowledge of PostgreSQL and PLPGSQL)

1. 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.
2. 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?

e.g.

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.

1. Write these singular (non-cursor) params into some table and then access from outside the function to know about last execution value
2. Include these other params as one of the column in REFCURSOR and get their values in the output when you access it
3. 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.

Answer 1

Honestly there are so many things wrong with this question, it's pretty hard to know where to start. Your function code is not valid and won't compile due to multiple issues like no variable declaration and almost certainly some record / type mismatches, as well as any return method. Since you don't include the full code, we can't tell if the message your getting back is from where you think it is (I don't think it is, but can't be 100% sure).

It sounds like both things are doable, but you're going to have to clean up the code and provide more info (or smaller test cases) to get real help. The one extra thing I was thinking to share was, if you want to use the refcursor approach, you can; here is an example of one way to do that using a stored procedure (would also work with a function), because these examples are hard to find: https://github.com/xzilla/pagila/commit/52c0ef17609c6d0a3a0e72d6de10ad9101314ffa (there are some other functions in this project as well that might server as good examples. HTH.