CODEWITHSUNDEEP
sql-serverpostgresql
Abhishek
Abhishek

Reputation: 3066

Postgresql function from a SQL Server stored procedure

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?

Upvotes: 0

Views: 208

Answers (1)

user330315
user330315

Reputation:

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)

Upvotes: 4

Related Questions