PostgreSQL SELECT on Output parameter

Question

I'm converting a MSSQL database to PostgreSQL and my experience with PostgreSQL is at the "noob" level. I have a stored procedure in MSSQL where I basically assign values to a group of OUT arguments. When I try to execute the below script to create the function, I get an error 'ERROR: column "closehi" does not exist' I am having trouble figuring out why I cannot SELECT into these OUT parameters. Here is the script:

CREATE FUNCTION public.getpogstats(IN symbol character varying, IN pogtypeid integer, OUT closehi numeric, OUT closelo numeric, OUT dayhi numeric, OUT daylo numeric, OUT s7dhi numeric, OUT s7dlo numeric, OUT t13hi numeric, OUT t13lo numeric, OUT close numeric, OUT firstdate timestamp without time zone)
    RETURNS record
    LANGUAGE 'sql'

AS $function$
SELECT closehi = ROUND(MAX(closeprice), 2), closelo = ROUND(MIN(closeprice), 2), dayhi = ROUND(MAX(dayhigh), 2), daylo = ROUND(MIN(daylow), 2), 
    s7dhi = ROUND(MAX(sevendaydp), 2), s7dlo = ROUND(MIN(sevendaydp), 2), t13hi = ROUND(MAX(thirteendaydp), 2), t13lo = ROUND(MIN(thirteendaydp), 2), firstdate = MIN(datadate)
FROM pogdata
JOIN symbol ON pogdata.symbolid = symbol.symbolid AND pogdata.pogtypeid = pogtypeid
WHERE symbol.symbol = symbol;

SELECT close = ROUND(ClosePrice, 2) FROM pogdata
JOIN symbol ON pogdata.symbolid = symbol.symbolid
WHERE datadate = (SELECT MAX(datadate) 
                  FROM pogdata JOIN Symbol ON pogdata.symbolid = symbol.symbolid AND pogdata.pogtypeid = pogtypeid 
                  WHERE symbol.symbol = symbol)
AND symbol.symbol = symbol

$function$;

ALTER FUNCTION public.getpogstats(character varying, integer)
    OWNER TO postgres;

After the suggestions I changed the portion of the function as follows:

SELECT ROUND(MAX(closeprice), 2), ROUND(MIN(closeprice), 2), ROUND(MAX(dayhigh), 2), ROUND(MIN(daylow), 2), 
    ROUND(MAX(sevendaydp), 2), ROUND(MIN(sevendaydp), 2), ROUND(MAX(thirteendaydp), 2), ROUND(MIN(thirteendaydp), 2),  MIN(datadate)
INTO closehi, closelo, dayhi, daylo, s7dhi, s7dlo, t13hi, t13lo, firstdate
FROM pogdata
JOIN symbol ON pogdata.symbolid = symbol.symbolid AND pogdata.pogtypeid = pogtypeid
WHERE symbol.symbol = symbol;

But now I get an error saying 'Error: syntax error at or near ","' and it is pointing at the comma directly after the closehi variable in the INTO target variable list.

change closehi = ROUND(MAX(closeprice), 2) to ROUND(MAX(closeprice), 2) into closehi and so on. and you miss closing semicolon in second query — Vao Tsun
– Vao Tsun, Commented May 8, 2017 at 7:14
Thanks. This was copied directly from my MSSQL stored proc, and thus far, was the only one that didn't work directly. — KFayal
– KFayal, Commented May 8, 2017 at 7:21

Laurenz Albe · Accepted Answer · 2017-05-08 07:16:57Z

2

The syntax is not

SELECT variable1 = expression1, variable2 = expression2 ...

but

SELECT expression1, expression2 INTO variable1, variable2 ...

See the documentation.

answered May 8, 2017 at 7:16

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL SELECT on Output parameter

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related