Shawn
Shawn

Reputation: 61

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

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);

Upvotes: 0

Views: 572

Answers (1)

Mike Organek
Mike Organek

Reputation: 12494

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;

Upvotes: 2

Related Questions