Postgresql function from a SQL Server stored procedure

Question

I have a procedure in SQL Server as follows:

I am trying to write a postgresql function exactly like the above. I tried something like this:

CREATE OR REPLACE FUNCTION getadmbyyear(IN a integer, IN b character varying)
  RETURNS TABLE(monname character varying, mon integer, adm integer, selected integer) AS
$BODY$

DECLARE testtypeid int;

select top 1 testtypeid := typeid from Reports_ReportMenu
where ID = $2

select MonName,Mon,Adm,
case when ROW_NUMBER() OVER(ORDER BY Mon,Adm) = '1' then
cast(1 as int) else cast(0 as int) end as Selected 
from eivTestAdms
where test_type_id=testtypeid and "YR"=$1 and Adm is not null
order by Mon,Adm

$BODY$
 LANGUAGE sql;

Here I am getting syntax error in the declare statement at int. Am I writing the function in the right format?

Curious why you use two queries rather than a join+group by or subquery here? — Joel Coehoorn
– Joel Coehoorn, Commented Sep 8, 2014 at 20:29

user330315 · Accepted Answer · 2014-09-08 20:26:10Z

4

You can't have variables in SQL function, those are only available when using PL/pgSQL.

But the variable isn't needed anyway. You can just put that into a sub-select.

Additionally cast(1 as int) is useless. 1 already is an integer, so is 0 so no casting needed there.

CREATE OR REPLACE FUNCTION getadmbyyear(IN a integer, IN b character varying)
  RETURNS TABLE(monname character varying, mon integer, adm integer, selected integer) 
AS
$body$
    select MonName, 
           Mon,
           Adm,
           case 
              when ROW_NUMBER() OVER (ORDER BY Mon,Adm) = 1 then 1 
              else 0 
           end as Selected 
    from eivTestAdms
    where test_type_id = (select testtypeid 
                          from Reports_ReportMenu 
                          where ID = $2
                          limit 1)
     and "YR"=$1 
     and Adm is not null
    order by Mon,Adm;
$body$
language sql;

But SQL Server's bit datatype is usually used as a poor man's boolean. So it should probably be a real boolean in Postgres. Which makes the case statement even shorter:

    select MonName, 
           Mon,
           Adm,
           ROW_NUMBER() OVER (ORDER BY Mon,Adm) = 1 as selected -- this is a boolean expression that will return true or false
    from eivTestAdms

You then need to adjust the function signature to:

RETURNS TABLE(monname character varying, mon integer, adm integer, selected boolean)

answered Sep 8, 2014 at 20:26

user330315

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

2 Comments

Abhishek Over a year ago

awesome...i changed the return type to boolean and its giving me a return type mismatch. Same case with integer as well

Abhishek Over a year ago

hi can you help me here?? stackoverflow.com/questions/25814827/…

Collectives™ on Stack Overflow

Postgresql function from a SQL Server stored procedure

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related