CODEWITHSUNDEEP
postgresqlfunction
Edijs Petersons
Edijs Petersons

Reputation: 567

Postgresql function with if statement

How can I make this pseudo code to work in Postgresql:

create or replace function getf(arg character varying(255)) returns int
as $$
if arg = 'a' then return 1;
else return 2;
$$ language sql;

Based on argument I need to return some values and there is no other table I need to query. Just need to build a logic inside function. Tried to replace if with when clause, but could not figure out how to do it.

Thanks!

Upvotes: 4

Views: 27717

Answers (4)

Tometzky
Tometzky

Reputation: 23890

create function getf(arg text) returns int
immutable strict language sql as $$
  select case 
    when arg = 'a' 
      then 1
      else 2 
    end
$$;

This is almost like Houari's answer, but:

  • it uses text as argument type — there's no point in limiting argument length in Postgres, in which there's no difference in representation;
  • it is immutable, so a database can cache it's result from the same argument, which can be faster;
  • it is strict, so it would not be run on NULL values and simply return NULL, as it's probably a programming error for it to be used on a NULL value anyway, and it could mask this error and make it harder to debug if it would return 2 for NULL.

Upvotes: 2

Patrick
Patrick

Reputation: 32234

create or replace function getf(arg character varying(255)) returns int as $$
begin
  if arg = 'a' then
    return 1;
  else 
    return 2;
  end if;
end; $$ language plpgsql;

Note that this is a PL/pgSQL function.

The online manual has an excellent chapter on PL/pgSQL. That should provide everything you need to get started writing procedural function with ample support for logical branching.

Upvotes: 14

joop
joop

Reputation: 4503

A pure SQL function. It is ugly, because SQL does not have IF. (you could use CASE instead, which is ugly, too) The DISTINCT FROM is needed to catch NULLs.

DROP function getf(_arg character varying(255) );
create or replace function getf(_arg character varying(255)) returns INTEGER
as $$
        SELECT 1::integer
        WHERE  _arg = 'a'
        UNION ALL
        SELECT 2::integer
        WHERE  _arg IS DISTINCT FROM 'a'
        ;
$$ language sql;

SELECT getf( 'a' );
SELECT getf( 'b' );
SELECT getf( NULL );

Upvotes: 3

Houari
Houari

Reputation: 5631

Using sql language, you can do it using case when:

create or replace function getf(arg character varying(255)) returns int as
 $$

select case 
        when arg = 'a' 
         then 1
         else 2 
       end

$$ language sql;

Upvotes: 5

Related Questions