AnswerBun.com

How to convert Postgres plpgsql user-defined function to LANGUAGE SQL user-defined function?

Stack Overflow Asked by Shawn on August 26, 2020

My understanding is, within Postgres Database, we can write SQL style user -created function and PlpgSQL style user-created function. And they should be able to translate from one to the other. First off, am I conceptually wrong?

Here is an example:

I was trying to convert such code below:

CREATE OR REPLACE FUNCTION getNthHighestSalary(N integer) RETURNS integer
AS $$
BEGIN
return (
    select distinct salary
    from employee
    order by salary
    limit 1 offset $1-1);
END;$$ LANGUAGE plpgsql;

into something like:

CREATE OR REPLACE FUNCTION getNthHighestSalary(N integer) RETURNS integer
AS
BEGIN
return (
    select distinct salary
    from employee
    order by salary
    limit 1 offset $1-1);
END; LANGUAGE SQL;

no matter how I tried, the code I converted to won’t work inside Postgres database, and always throws weird syntax error.

so how to convert the piece of code above to a viable Standard SQL function which is able to run within Postgres database? especially please explain where the problem is and what’s the major difference between Standard SQL and Plpgsql syntax in the Postgres Database environment. Thanks a lot

BTW, here’s the code for creating test table and inserting test data:

create table Employee
(
id varchar(255) PRIMARY KEY,
Salary numeric
);
insert into Employee values('1',100),('2',200),('3',300);

One Answer

If you want to use LANGUAGE SQL, then there are a couple of changes you have to make.

First is to get rid of the BEGIN and END.

Second is to simply state the SELECT query without the RETURN keyword.

There were some other problems: You should order by salary desc, the return type is numeric rather than integer, and you need to escape the ; character, so enclose it with $$ as you do the plpgsql functions.

CREATE OR REPLACE FUNCTION getNthHighestSalary(N integer) RETURNS numeric
AS $$
    select distinct salary
      from employee
     order by salary desc
     limit 1 offset $1-1;
$$ LANGUAGE SQL;

Correct answer by Mike Organek on August 26, 2020

Add your own answers!

Related Questions

How can I get different margins when appending divs in CSS?

4  Asked on February 4, 2021 by michaelstackquestion

     

Getting error while running merged jtl files

2  Asked on February 4, 2021 by ajij-shaikh

   

Why don’t need to use free() function in this case?

2  Asked on February 4, 2021 by akrilmokus

     

how to show data of databse in navigation bar on laravel 7

2  Asked on February 4, 2021 by fahad-munir

     

String formatting: optional section

1  Asked on February 3, 2021 by cerno

     

Multiple table to encode json and display

1  Asked on February 3, 2021 by j-wujeck

   

i need to remove duplicated comments from every post

1  Asked on February 3, 2021 by rabie_alkholi

         

Why is C is much slower as compared to Java?

1  Asked on February 3, 2021 by jaysmito-mukherjee

       

Server returned HTTP response code: 429 for URL JAVA Reddit JSON

2  Asked on February 3, 2021 by luke-prior

     

How to measure sequential memory read speed in C/C++

1  Asked on February 3, 2021 by sz-ppeter

 

Ask a Question

Get help from others!

© 2022 AnswerBun.com. All rights reserved. Sites we Love: PCI Database, MenuIva, UKBizDB, Menu Kuliner, Sharing RPP