Multiple records load between UDFS

Hi. I have taken your script and modified it. Based on my tests, it works using standard Postgresql - hopefully it will work for you as well. I won't comment in full on the approach you are taking as you have specifically asked me not to, however this would not be my preferred method (and I appreciate that your requirements may well be complex).

drop table if exists customer;
drop table if exists payment;

create table customer (
  customer_id integer,
  first_name character varying (45),
  last_name character varying (45),
  email character varying (45),
  other_info text
);

create table payment (
  payment_id integer,
  rental_id integer,
  customer_id integer,
  amount numeric(5,2)
);

insert into customer values (1, 'Fred', 'Bloggs', 'fred@email.com', 'Hello world 1');
insert into customer values (2, 'Joe', 'Smith', 'joe@email.com', 'Hello world 2');
insert into customer values (3, 'Alan', 'Jones', 'alan@email.com', 'Hello world 3');
insert into customer values (4, 'Bill', 'Williams', 'bill@email.com', 'Hello world 4');

insert into payment values (1, 1, 1, 20.40);
insert into payment values (2, 2, 2, 23.60);
insert into payment values (3, 3, 3, 26.80);
insert into payment values (4, 4, 4, 25.00);

drop type if exists cust_type cascade;

create type cust_type AS (
  customer_id integer,
  first_name character varying(45),
  last_name character varying(45),
  email character varying(45)  
); 

create or replace function cust_fun(
  cust_id int) returns cust_type as $$
declare
  result cust_type;
begin
  select
      customer_id, first_name, last_name,
      email
    into
      result
  from
    customer
  where
    customer_id = cust_id; 
  return result;
end; $$ language plpgsql volatile security invoker;

drop type if exists payment_type cascade;

create type payment_type as (
  payment_id integer,
  rental_id integer,
  amount numeric(5,2),
  first_name character varying(45),
  last_name character varying(45)
); 

create or replace function payment_fun(
  cust_id int) returns payment_type as $$
declare
  result payment_type;
begin
  create temp table temp_cust(
    customer_id integer,
    first_name character varying(45),
    last_name character varying(45),
    email character varying(45)
  ) on commit drop;
  insert into temp_cust
    select 
      * 
    from 
      cust_fun(cust_id);
  select
      p.payment_id,
      p.rental_id,
      p.amount,
      tc.first_name,
      tc.last_name
   into
      result
   from
      temp_cust tc
      inner join public.payment p on tc.customer_id = p.customer_id;
   return result;
 end; $$ language plpgsql volatile security invoker;

Select allOpen in new window

If I run

select payment_fun(2);

Select allOpen in new window

I see the desired result. When the temporary table is created, you can set rules that define if the table is emptied or dropped on commit. Naturally you could control what happens to said table during your session.

Thank you very much for the reply. It is very good for one record. Just wanted to extend to a case where first function cust_fun() returns multiple records and in the second function call we have insert these returned multiple records into a temp table and with join condition in second function we should also return multiple records as out put. I really appreciate if get a quick response. Again thanks

I will look at making the changes requested a when I return to my office (likely to be around 3-4 hours from now). Someone else may step in in the meantime.

Hi Martyn, I  have tried as below using ref cursor to output multiple records data set , I have used same example which you have provided and modified.  When we use ref cursor the output needs to fetched from cursor and i am not sure how can we use this fetched output and insert into temp table in a calling function. Please find below code.  Could you please advise if you  get a chance.

Thanks
Venkat

DROP TABLE IF EXISTS CUSTOMER;
DROP TABLE IF EXISTS PAYMENT;

CREATE TABLE CUSTOMER (
  CUSTOMER_ID INTEGER,
  FIRST_NAME CHARACTER VARYING (45),
  LAST_NAME CHARACTER VARYING (45),
  EMAIL CHARACTER VARYING (45),
  OTHER_INFO TEXT
);

CREATE TABLE PAYMENT (
  PAYMENT_ID INTEGER,
  RENTAL_ID INTEGER,
  CUSTOMER_ID INTEGER,
  AMOUNT NUMERIC(5,2)
);

INSERT INTO CUSTOMER VALUES (1, 'FRED', 'BLOGGS', 'FRED@EMAIL.COM', 'HELLO WORLD 1');
INSERT INTO CUSTOMER VALUES (2, 'JOE', 'SMITH', 'JOE@EMAIL.COM', 'HELLO WORLD 2');
INSERT INTO CUSTOMER VALUES (3, 'ALAN', 'JONES', 'ALAN@EMAIL.COM', 'HELLO WORLD 3');
INSERT INTO CUSTOMER VALUES (4, 'BILL', 'WILLIAMS', 'BILL@EMAIL.COM', 'HELLO WORLD 4');
INSERT INTO CUSTOMER VALUES (5, 'MIKE', 'TAYLOR', 'MIKE@EMAIL.COM', 'HELLO WORLD 5');
INSERT INTO CUSTOMER VALUES (6, 'FRED12', 'BLOGGS12', 'FRED12@EMAIL.COM', 'HELLO WORLD 6');
INSERT INTO CUSTOMER VALUES (7, 'JOE12', 'SMITH12', 'JOE12@EMAIL.COM', 'HELLO WORLD 7');
INSERT INTO CUSTOMER VALUES (8, 'ALAN12', 'JONES12', 'ALAN12@EMAIL.COM', 'HELLO WORLD 8');
INSERT INTO CUSTOMER VALUES (9, 'BILL12', 'WILLIAMS12', 'BILL12@EMAIL.COM', 'HELLO WORLD 9');
INSERT INTO CUSTOMER VALUES (10, 'MIKE12', 'TAYLOR12', 'MIKE12@EMAIL.COM', 'HELLO WORLD 10');

INSERT INTO PAYMENT VALUES (1, 1, 1, 20.40);
INSERT INTO PAYMENT VALUES (2, 2, 2, 23.60);
INSERT INTO PAYMENT VALUES (3, 3, 3, 26.80);
INSERT INTO PAYMENT VALUES (4, 4, 4, 25.00);
INSERT INTO PAYMENT VALUES (5, 5, 5, 20.40);
INSERT INTO PAYMENT VALUES (6, 6, 6, 23.60);
INSERT INTO PAYMENT VALUES (7, 7, 7, 26.80);
INSERT INTO PAYMENT VALUES (8, 8, 8, 25.00);
INSERT INTO PAYMENT VALUES (9, 9, 9, 26.80);
INSERT INTO PAYMENT VALUES (10,10,10,25.00);

--DROP TYPE IF EXISTS CUST_TYPE CASCADE;

--CREATE TYPE CUST_TYPE AS (
--  CUSTOMER_ID INTEGER,
 -- FIRST_NAME CHARACTER VARYING(45),
--  LAST_NAME CHARACTER VARYING(45),
--  EMAIL CHARACTER VARYING(45)  
--); 


DROP FUNCTION IF EXISTS CUST_FUN();

CREATE OR REPLACE FUNCTION CUST_FUN(refcursor)
   RETURNS SETOF REFCURSOR AS $$
DECLARE

 REF2 REFCURSOR;
BEGIN 
REF2 := $1;
OPEN REF2 FOR
  SELECT
      CUSTOMER_ID, FIRST_NAME, LAST_NAME,
      EMAIL
    FROM
    CUSTOMER; 
  RETURN NEXT REF2;
  
END; 
$$ LANGUAGE PLPGSQL VOLATILE SECURITY INVOKER;

DROP FUNCTION IF EXISTS PAYMENT_FUN();

CREATE OR REPLACE FUNCTION PAYMENT_FUN(refcursor) 
    RETURNS SETOF REFCURSOR AS $$
DECLARE
  REF1 REFCURSOR;
  
BEGIN

REF1 := $1;

  CREATE TEMP TABLE TEMP_CUST(
    CUSTOMER_ID INTEGER,
    FIRST_NAME CHARACTER VARYING(45),
    LAST_NAME CHARACTER VARYING(45),
    EMAIL CHARACTER VARYING(45)
  ) ON COMMIT DROP;

    SELECT 
      * 
    FROM 
      CUST_FUN($1);

  INSERT INTO TEMP_CUST
      FETCH ALL FROM $1;
  
   OPEN REF1 FOR SELECT
      P.PAYMENT_ID,
      P.RENTAL_ID,
      P.AMOUNT,
      TC.FIRST_NAME,
      TC.LAST_NAME
    FROM
      TEMP_CUST TC
      INNER JOIN PAYMENT P ON TC.CUSTOMER_ID = P.CUSTOMER_ID;
   RETURN NEXT REF1;
   
 END; 
 COMMIT;
 $$ 
 LANGUAGE PLPGSQL VOLATILE SECURITY INVOKER;

Select allOpen in new window

Hi. Sorry that I was unable to look last week. I'll take a look shortly and comment.

If you are wanting to handle multiple rows that are returned by payment_fun, there's no need to use a refcursor as I see it. If you specifically want to use a refcursor let me know. I have modified my code so that payment_fun returns more than one row and you can use it in a select function.

You can change cust_fun in a similar fashion, but since as it stands you are looking up by what I presume is the primary key, it would only ever return one row (or no rows).

drop table if exists customer;
drop table if exists payment;

create table customer (
  customer_id integer,
  first_name character varying (45),
  last_name character varying (45),
  email character varying (45),
  other_info text
);

create table payment (
  payment_id integer,
  rental_id integer,
  customer_id integer,
  amount numeric(5,2)
);

insert into customer values (1, 'Fred', 'Bloggs', 'fred@email.com', 'Hello world 1');
insert into customer values (2, 'Joe', 'Smith', 'joe@email.com', 'Hello world 2');
insert into customer values (3, 'Alan', 'Jones', 'alan@email.com', 'Hello world 3');
insert into customer values (4, 'Bill', 'Williams', 'bill@email.com', 'Hello world 4');

insert into payment values (1, 1, 1, 20.40);
insert into payment values (2, 2, 2, 23.60);
insert into payment values (3, 3, 3, 26.80);
insert into payment values (4, 4, 4, 25.00);
insert into payment values (5, 5, 1, 25.00);
insert into payment values (6, 6, 2, 30.00);

drop type if exists cust_type cascade;

create type cust_type AS (
  customer_id integer,
  first_name character varying(45),
  last_name character varying(45),
  email character varying(45)  
); 

create or replace function cust_fun(
  cust_id int) returns cust_type as $$
declare
  result cust_type;
begin
  select
      customer_id, first_name, last_name,
      email
    into
      result
  from
    customer
  where
    customer_id = cust_id; 
  return result;
end; $$ language plpgsql volatile security invoker;

drop type if exists payment_type cascade;

create type payment_type as (
  payment_id integer,
  rental_id integer,
  amount numeric(5,2),
  first_name character varying(45),
  last_name character varying(45)
); 

create or replace function payment_fun(
  cust_id int) returns setof payment_type as $$
declare
  payRec payment_type;
begin
  create temp table temp_cust(
    customer_id integer,
    first_name character varying(45),
    last_name character varying(45),
    email character varying(45)
  ) on commit drop;
  insert into temp_cust
    select 
      * 
    from 
      cust_fun(cust_id);
  for payRec in 
    select
      p.payment_id,
      p.rental_id,
      p.amount,
      tc.first_name,
      tc.last_name
    from
      temp_cust tc
      inner join public.payment p on tc.customer_id = p.customer_id
   loop
     return next payRec;
   end loop;
 end; $$ language plpgsql volatile security invoker;

Select allOpen in new window

Hi Martyn,
Thanks for the update,it is very good for small volume of data. we have another requirement for large volume of the data, just wanted to load multiple records  from output of function into temp table and process data and output set of records from another calling function. please find below code. not sure what is the best approach I was thinking of REF CURSOR.

requirement is : cust_fun()  simply out puts whole customer tabe data and payment_fun() loads data into temp table and joins with payment table with customer_id and output whole data set , there are no input parameters to restrict data. Please advise.

this is the error we keep getting when try with full load approach.

ERROR: 0A000: function cannot execute on segment because it accesses relation "core.customer"  (entry db 111.222.3.01 pid=22992)

Perhaps the problem you are seeing is a difference between Postgresql and Greenplum. I found a reference to the message that you mention and it implies that doing something along the lines of:

select 1, cust_fun(1) from table_xxx

Select allOpen in new window

would fail and that it should be rewritten as:

select 1, (select * from cust_fun(1)) from table_xxx

Select allOpen in new window

In other words, the function select should be made into a sub query.

If you have no parameters to restrict data, I think you are going to run into other issues. Carrying out operations like we are doing above without any possible query optimisations is going to result in very sub-optimal performance. If cust_fun is going to retrieve all customer table data, why create a function? Why not just select the data? Perhaps I am missing something.

I am heading home, I will try your option. We have lot of store procs which does certain operations and out puts huge volume of data on Netezza system , now we are migrating these store procs into green plum UDFs . I am trying to mimic  a scenario for us where a store proc/udf returns large data set and we wanted to work on this out put in another calling  function after certain operations on this data set , we need to output again  large volume of data  from second/calling function. If you have any  better ideas to achieve this, please advise, it  will be greatly appreciated.

Thanks
Venkat

Hi,  It is working fine if function return single column  and single row.  like below otherwise getting  error
 (ERROR:  subquery must return only one column)
select c.*, (select customer_id from cust_fun() limit 1) from customer c
This is fine for single record or looping through  record sets for small volume, no issues at all.

But,if we get a solution/approach for batch/bulk loads between UDFs as per previous comment that would  be greatly appreciated.

The post that I was reading clearly did not check about subqueries in the above manner. What happens if you use a pattern like:

select 
  1, cf.*
from
  (select * from cust_fun(1)) cf

Select allOpen in new window

If this works, it may be possible to adapt the query to do what you are looking for. Naturally, my example is of limited use.

Hi , When dealing with large volumes of data across UDFs as per earlier comment any better way of achieving, if you any ideas please let me know.
Thanks for your help so far.

In your payment_fun, I imagine that doing an "insert into temp_cust fetch all in refcur" (where refcur is a refcursor), will fail because there is no correlation between the temporary table and the refcursor. How would the database know what columns are required and how to map between the refcursor and the temporary table?

Since cust_fun is returning a refcursor, I am not sure why you need a temporary table since you could simply pull rows from that. Are you concerned about the number of rows? In that case, surely it's best to build the temporary table in cust_fun, rather than attempting to do so across function calls. This temporary table could then be used in payment_fun

The above being said, I have modified your code and my final test does work. Maybe you can use this as a template? I create the temporary table in cust_fun (since it knows about the structure that I require). I am not sure that you can do what you want with refcursors in both functions since your insert into .... using the refcursor would fail (how would the database know what columns to select and insert?)

If I knew more about what you were trying to achieve, I may be able to help more but I am not sure that the approach we are discussing here is necessarily the best. Hopefully I have answered your question, though.

DROP TABLE IF EXISTS CUSTOMER;
DROP TABLE IF EXISTS PAYMENT;

CREATE TABLE CUSTOMER (
  CUSTOMER_ID INTEGER,
  FIRST_NAME CHARACTER VARYING (45),
  LAST_NAME CHARACTER VARYING (45),
  EMAIL CHARACTER VARYING (45),
  OTHER_INFO TEXT
);

CREATE TABLE PAYMENT (
  PAYMENT_ID INTEGER,
  RENTAL_ID INTEGER,
  CUSTOMER_ID INTEGER,
  AMOUNT NUMERIC(5,2)
);

INSERT INTO CUSTOMER VALUES (1, 'FRED', 'BLOGGS', 'FRED@EMAIL.COM', 'HELLO WORLD 1');
INSERT INTO CUSTOMER VALUES (2, 'JOE', 'SMITH', 'JOE@EMAIL.COM', 'HELLO WORLD 2');
INSERT INTO CUSTOMER VALUES (3, 'ALAN', 'JONES', 'ALAN@EMAIL.COM', 'HELLO WORLD 3');
INSERT INTO CUSTOMER VALUES (4, 'BILL', 'WILLIAMS', 'BILL@EMAIL.COM', 'HELLO WORLD 4');
INSERT INTO CUSTOMER VALUES (5, 'MIKE', 'TAYLOR', 'MIKE@EMAIL.COM', 'HELLO WORLD 5');
INSERT INTO CUSTOMER VALUES (6, 'FRED12', 'BLOGGS12', 'FRED12@EMAIL.COM', 'HELLO WORLD 6');
INSERT INTO CUSTOMER VALUES (7, 'JOE12', 'SMITH12', 'JOE12@EMAIL.COM', 'HELLO WORLD 7');
INSERT INTO CUSTOMER VALUES (8, 'ALAN12', 'JONES12', 'ALAN12@EMAIL.COM', 'HELLO WORLD 8');
INSERT INTO CUSTOMER VALUES (9, 'BILL12', 'WILLIAMS12', 'BILL12@EMAIL.COM', 'HELLO WORLD 9');
INSERT INTO CUSTOMER VALUES (10, 'MIKE12', 'TAYLOR12', 'MIKE12@EMAIL.COM', 'HELLO WORLD 10');

INSERT INTO PAYMENT VALUES (1, 1, 1, 20.40);
INSERT INTO PAYMENT VALUES (2, 2, 2, 23.60);
INSERT INTO PAYMENT VALUES (3, 3, 3, 26.80);
INSERT INTO PAYMENT VALUES (4, 4, 4, 25.00);
INSERT INTO PAYMENT VALUES (5, 5, 5, 20.40);
INSERT INTO PAYMENT VALUES (6, 6, 6, 23.60);
INSERT INTO PAYMENT VALUES (7, 7, 7, 26.80);
INSERT INTO PAYMENT VALUES (8, 8, 8, 25.00);
INSERT INTO PAYMENT VALUES (9, 9, 9, 26.80);
INSERT INTO PAYMENT VALUES (10,10,10,25.00);

--DROP TYPE IF EXISTS CUST_TYPE CASCADE;

--CREATE TYPE CUST_TYPE AS (
--  CUSTOMER_ID INTEGER,
 -- FIRST_NAME CHARACTER VARYING(45),
--  LAST_NAME CHARACTER VARYING(45),
--  EMAIL CHARACTER VARYING(45)  
--);


DROP FUNCTION IF EXISTS CUST_FUN();

CREATE OR REPLACE FUNCTION CUST_FUN () RETURNS VOID AS $$
DECLARE
  REFCUR REFCURSOR;
BEGIN
  CREATE TEMP TABLE TEMP_CUST(
    CUSTOMER_ID INTEGER,
    FIRST_NAME CHARACTER VARYING(45),
    LAST_NAME CHARACTER VARYING(45),
    EMAIL CHARACTER VARYING(45)
  ) ON COMMIT DROP;

  INSERT INTO TEMP_CUST  
    SELECT
      CUSTOMER_ID, FIRST_NAME, LAST_NAME, EMAIL
    FROM
      CUSTOMER;
      
END;
$$ LANGUAGE PLPGSQL VOLATILE SECURITY INVOKER;

DROP FUNCTION IF EXISTS PAYMENT_FUN();

CREATE OR REPLACE FUNCTION PAYMENT_FUN() RETURNS REFCURSOR AS $$
DECLARE
  REFCUR REFCURSOR;
BEGIN
  PERFORM CUST_FUN();
  OPEN REFCUR FOR SELECT
      P.PAYMENT_ID,
      P.RENTAL_ID,
      P.AMOUNT,
      TC.FIRST_NAME,
      TC.LAST_NAME
    FROM
      TEMP_CUST TC
      INNER JOIN PAYMENT P ON TC.CUSTOMER_ID = P.CUSTOMER_ID;
   RETURN REFCUR;
END;
$$
LANGUAGE PLPGSQL VOLATILE SECURITY INVOKER;


BEGIN;

SELECT PAYMENT_FUN();

FETCH ALL IN "<unnamed portal 5>" -- Should be the refcursor name returned from the above select

Select allOpen in new window

Thanks for your help. I have no more questions. We are working on lot of postgres  development. I will reach out in this forum if I have any further questions.

Regards
Venkat

This is very helpful but not exactly is the solution. we will try ourselves  and if required reach out.

Thanks for your help. I have no more questions. We are working on lot of postgres  development. I will reach out in this forum if I have any further questions.

Regards
Venkat

You said you had no more questions and I have nothing more to add now. Best of luck.

It's a little unfair to post a question, receive an answer to that question and to continue updating the question so that it leads to further answers being required. Your initial question made no reference to the requirement to use refcursors and your initial example code did not include the use of them either. I have effectively given two different answers as your question developed over time. Changing the question devalues previously given answers, as I see it.

I suggest you post your latest amendment as another question. It is much more likely that you will receive a response (from other experts as well). Quite some time has been invested in responding to you so far and a developing question is probably best answered as a series of smaller questions rather than one that simply grows more complicated over time.

A good deal of time was spent answering this question and the scope changed several times. It has content of value and I do not accept that it should be deleted. Thanks.

Instead of leaving this question as unfinished/orphaned , I thought of deleting from here and take relevant/same content and use as the background and open new question but  objection raised by expert for deleting.  There is an initial exchange which worked for simple scenario and I see there is  some effort. just to appreciate the effort by the expert, I decided to mark this question as solved.  Even though it did not solve fully.

Thanks for your help.