I am trying to create and populate a temp table inside a procedure to save some intermediate state of the data. Please see the example code.
CREATE or replace procedure sp_getOfficeStateData (
inout PROCESSING_DATE_YMD CHAR(06),
inout TYPE_OF_RUN VARCHAR (1),
inout CHECK_PROCESSING_UNIT VARCHAR (1),
inout CHECK_AUTO_DIVISION VARCHAR (1),
inout PASS_VALUE_OFFICE_DIV_SF_DIV VARCHAR (3),
inout PASS_STATE_MIS_STAE_P_UNIT VARCHAR (3),
inout PASS_PROCESSING_UNIT VARCHAR (2),
inout PASS_STATE_INITIALS VARCHAR (2),
inout PASS_GEOGRAPHIC_STATE VARCHAR (2),
inout PASS_ALPH_ORDER_STATE_NO VARCHAR (2),
inout PASS_ALPH_DIVISION VARCHAR (1),
inout PASS_STATE_NAME VARCHAR (20),
inout PASS_STATE_NAME VARCHAR (20),
inout PASS_STATE_ABBREVIATION VARCHAR (9),
inout PASS_OFFICE_NAME VARCHAR (20),
inout PASS_OFFICE_ABBREVIATION VARCHAR (9),
inout PASS_GEO_STATE_NAME VARCHAR (20),
inout PASS_GEO_STATE_ABBREV VARCHAR (9),
inout PASS_AUTO_DIVISION_NAME VARCHAR (9),
inout PASS_OFFICE_QUALIFIER VARCHAR (6),
inout PASS_STREET_ADDRESS VARCHAR (30),
inout PASS_CITY_STATE_ZIP VARCHAR (30),
inout PASS_ZONE VARCHAR (1) ,
inout PASS_PRINT_STATE VARCHAR (3) ,
inout PASS_STATE_SUBSCRIPT DECIMAL(3,0),
inout PASS_PRINT_SUBSCRIPT DECIMAL(3,0),
inout PASS_JOB_NUMBER_FIL5_OFFI_IND1_FILL2 VARCHAR (8),
inout PASS_NUMBER_OF_STATES DECIMAL(3,0),
inout PASS_NUMBER_OF_DIVISIONS DECIMAL(3,0),
inout PASS_NO_STATE_WITHIN_DIV DECIMAL(3,0),
inout PASS_MICDJ_COMPANY VARCHAR (10),
inout COMPANIES VARCHAR (10),
inout PASS_ILLER VARCHAR (19),
inout PASS_SPECIAL_RUN VARCHAR (1),
inout PASS_OFFICE_PHONE_ACODE_PREFIX_NUM VARCHAR (10),
inout PASS_CHECK_PREV_DATA VARCHAR (1),
inout PASS_FILL_OCCURS_PART VARCHAR (1),
inout PASS_USE_REGION_DATES VARCHAR (1))
RETURNS TABLE(STATES_WITHIN_OFFI_SF_DISION VARCHAR (3), STATES_WITHIN_MIS_ST_P_UNIT VARCHAR(3),STATES_WITHIN_PROCESSING_UNIT VARCHAR (2),
STATES_WITHIN_STATE_INITIALS VARCHAR (2),
STATES_WITHIN_GEOGRAPHIC_STATE VARCHAR (2),
STATES_WITHIN_ALPH_ORDER_STATE_NO VARCHAR (2).
STATES_WITHIN_ALPH_DIVISION VARCHAR (1),
STATES_WITHIN_STATE_NAME VARCHAR (20),
STATES_WITHIN_STATE_ABBREVIATION VARCHAR (9),
STATES_WITHIN_OFFICE_NAME VARCHAR (20),
STATES_WITHIN_OFFICE_ABBREVIATION VARCHAR (9),
STATES_WITHIN_GEO_STATE_NAME VARCHAR (20),
STATES_WITHIN_GEO_STATE_ABBREV VARCHAR (9),
STATES_WITHIN_AUTO_DIVISION_NAME VARCHAR (9),
STATES_WITHIN_PRINT_STATE VARCHAR (3),
STATES_WITHIN_STATE_SUBSCRIPT DECIMAL (3,0),
STATES_WITHIN_PRINT_SUBSCRIPT DECIMAL (3,0),
STATES_WITHIN_FILLER VARCHAR (48),
STATES_WITHIN_COMPANIES VARCHAR (5))
as
$$
DECLARE
begin
CREATE TEMP TABLE IF NOT EXISTS ALL_OF_THE_OFF_OR_ST_DATA(
STATES_WITHIN_OFFI_SF_DISION VARCHAR (3), STATES_WITHIN_MIS_ST_P_UNIT VARCHAR(3),STATES_WITHIN_PROCESSING_UNIT VARCHAR (2),
STATES_WITHIN_STATE_INITIALS VARCHAR (2),
STATES_WITHIN_GEOGRAPHIC_STATE VARCHAR (2),
STATES_WITHIN_ALPH_ORDER_STATE_NO VARCHAR (2).
STATES_WITHIN_ALPH_DIVISION VARCHAR (1),
STATES_WITHIN_STATE_NAME VARCHAR (20),
STATES_WITHIN_STATE_ABBREVIATION VARCHAR (9),
STATES_WITHIN_OFFICE_NAME VARCHAR (20),
STATES_WITHIN_OFFICE_ABBREVIATION VARCHAR (9),
STATES_WITHIN_GEO_STATE_NAME VARCHAR (20),
STATES_WITHIN_GEO_STATE_ABBREV VARCHAR (9),
STATES_WITHIN_AUTO_DIVISION_NAME VARCHAR (9),
STATES_WITHIN_PRINT_STATE VARCHAR (3),
STATES_WITHIN_STATE_SUBSCRIPT DECIMAL (3,0),
STATES_WITHIN_PRINT_SUBSCRIPT DECIMAL (3,0),
STATES_WITHIN_FILLER VARCHAR (48),
STATES_WITHIN_COMPANIES VARCHAR (5)
) ON COMMIT DROP;
RETURN QUERY SELECT * FROM ALL_OF_THE_OFF_OR_ST_DATA;
end;
$$
language
plpgsql;
Please help me with this. When trying to create this Stored Procedure the database returns this error message: ERROR: syntax error at or near “TABLE”
LINE 41: RETURNS TABLE(STATES_WITHIN_OFFI_SF_DISION VARCHAR (3), …
^
SQL state: 42601
Character: 2327
New contributor
Sandeep Kumar is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.