cancel
Showing results for 
Search instead for 
Did you mean: 

Porting Oracle PIPELINE to EDB (?)

SOLVED
Adventurer

Porting Oracle PIPELINE to EDB (?)

Hello all: I am new to EDB and to PostgreSQL in general. We are looking into porting the following Oracle function to EDB (see code block below.)

 

I understand PostgreSQL does not have PIPELINE, but only has RETURN SETOF, as per this article

https://community.postgresrocks.net/t5/The-Knowledgebase/Oracle-Workaround-PIPELINED/ta-p/152

 

But I get lost when it comes to syntax like PIPLINE AS vs PIPELINE IS TYPE, etc... and we have not been able to get it to compile on EDB.

Any advice would be appreciated. Thanks!

 

Oracle Script Below:
CREATE FUNCTION ecaps.f_calendar_by_month ( p_year NUMBER, p_month NUMBER ) RETURN CALENDAR_TYPE_1_SET PIPELINED IS TYPE REF_CURSOR IS REF CURSOR; TYPE CALENDAR_TYPE_1_REC IS RECORD ( CALENDAR_DT DATE, WEEK_OF_MONTH_NBR NUMBER(1), WEEKDAY_NBR NUMBER(1), WEEKEND_FL NUMBER(1), HOLIDAY_FL NUMBER(1), ITEM_01 VARCHAR2(30), ITEM_02 VARCHAR2(30) ); SQL_1_CUR REF_CURSOR; SQL_1_CUR_REC CALENDAR_TYPE_1_REC; PIPE_ROW_OUT CALENDAR_TYPE_1 := CALENDAR_TYPE_1(NULL,NULL,NULL,NULL,NULL,NULL,NULL); days_in_month NUMBER(2) := 0; month_array_date ARRAY_DATE := ARRAY_DATE(); week_of_month_nbr NUMBER(1) := 0; day_of_week_nbr NUMBER(1) := 0; BEGIN if (p_month >= 1) and (p_month <= 12) and (p_year >= 1996) and (p_year <= 3000) then if (p_month in (1,3,5,7,8,10,12)) then days_in_month := 31; elsif (p_month in (4,6,9,11)) then days_in_month := 30; elsif (mod(p_year,4) = 0) then days_in_month := 29; else days_in_month := 28; end if; for day_count in 1 .. days_in_month loop month_array_date.extend; month_array_date(month_array_date.last) := to_date(to_char(p_month,'00') || '/' || to_char(day_count,'00') || '/' || to_char(p_year,'0000'),'mm/dd/yyyy'); end loop; OPEN sql_1_cur for select NULL,NULL,NULL,NULL,NULL,NULL,NULL from dual where 1 = 2; for day_count in 1 .. days_in_month loop SQL_1_CUR_REC.CALENDAR_DT := month_array_date(day_count); week_of_month_nbr := ECAPS.WORK_WEEK_OF_MONTH(month_array_date(day_count)); SQL_1_CUR_REC.WEEK_OF_MONTH_NBR := week_of_month_nbr; day_of_week_nbr := to_number(to_char(month_array_date(day_count),'d')); SQL_1_CUR_REC.WEEKDAY_NBR := day_of_week_nbr; if (day_of_week_nbr = 1) or (day_of_week_nbr = 6) or (day_of_week_nbr = 7) then SQL_1_CUR_REC.WEEKEND_FL := 1; else SQL_1_CUR_REC.WEEKEND_FL := 0; end if; if ecaps.HOLIDAY(month_array_date(day_count)) = 1 then SQL_1_CUR_REC.HOLIDAY_FL := 1; SQL_1_CUR_REC.ITEM_01 := 'Holiday'; else SQL_1_CUR_REC.HOLIDAY_FL := 0; SQL_1_CUR_REC.ITEM_01 := ''; end if; SQL_1_CUR_REC.ITEM_02 := ''; FETCH sql_1_cur INTO sql_1_cur_rec; PIPE_ROW_OUT.CALENDAR_DT := SQL_1_CUR_REC.CALENDAR_DT; PIPE_ROW_OUT.WEEK_OF_MONTH_NBR := SQL_1_CUR_REC.WEEK_OF_MONTH_NBR; PIPE_ROW_OUT.WEEKDAY_NBR := SQL_1_CUR_REC.WEEKDAY_NBR; PIPE_ROW_OUT.WEEKEND_FL := SQL_1_CUR_REC.WEEKEND_FL; PIPE_ROW_OUT.HOLIDAY_FL := SQL_1_CUR_REC.HOLIDAY_FL; PIPE_ROW_OUT.ITEM_01 := SQL_1_CUR_REC.ITEM_01; PIPE_ROW_OUT.ITEM_02 := SQL_1_CUR_REC.ITEM_02; PIPE ROW (PIPE_ROW_OUT); end loop; CLOSE sql_1_cur; end if; RETURN; END f_calendar_by_month;

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Explorer

Re: Porting Oracle PIPELINE to EDB (?)

For CREATE FUNCTION syntax AS or IS have the same meaning. So you don't have to be confused.

The part which you have to ensure is RETURN and PIPELINED.

You should remove the PIPELINED keyword and should use RETURN SETOF CALENDAR_TYPE_1_SET

 

 
2 REPLIES
Highlighted
Explorer

Re: Porting Oracle PIPELINE to EDB (?)

For CREATE FUNCTION syntax AS or IS have the same meaning. So you don't have to be confused.

The part which you have to ensure is RETURN and PIPELINED.

You should remove the PIPELINED keyword and should use RETURN SETOF CALENDAR_TYPE_1_SET

 

 
Adventurer

Re: Porting Oracle PIPELINE to EDB (?)

Thank you @VibhorKumar.  I was able to get the object to compile with the following changes (below.)  

 

RETURN SETOF ECAPS.CALENDAR_TYPE_1_SET IS
-- PIPELINED IS
And...
--PIPE ROW (PIPE_ROW_OUT);
RETURN NEXT (PIPE_ROW_OUT);

Thanks again!