Query on Return Statement - PostgreSQL

Question

I have this question, I was doing some migration from SQL Server to PostgreSQL 12.

The scenario, I am trying to accomplish:

The function should have a RETURN Statement, be it with SETOF 'tableType' or RETURN TABLE ( some number of columns )

The body starts with a count of records, if there is no record found based on input parameters, then simply Return Zero (0), else, return the entire set of record defined in the RETURN Statement.

The Equivalent part in SQL Server or Oracle is: They can just put a SELECT Statement inside a Procedure to accomplish this. But, its a kind of difficult in case of PostgreSQL.

Any suggestion, please.

What I could accomplish still now - If no record found, it will simply return NULL, may be using PERFORM, or may be selecting NULL as column name for the returning tableType columns.

I hope I am clear !

What I want is something like -

============================================================

CREATE OR REPLACE FUNCTION public.get_some_data(
    id integer)
    RETURNS TABLE ( id_1 integer, name character varying )

    LANGUAGE 'plpgsql'



AS $BODY$

 DECLARE 

 p_id alias for $1;
 v_cnt integer:=0;

BEGIN

    SELECT COUNT(1) FROM public.exampleTable e
    WHERE id::integer = e.id::integer;

    IF v_cnt= 0 THEN
        SELECT 0;
    ELSE

    SELECT
       a.id, a.name
        public.exampleTable a 
        where a.id = p_id;
END;
$BODY$;

Answer 1

One more hack to get as close as possible to what you want. But I will repeat what others have told you: You cannot do what you want directly. Just because MS SQL Server lets you get away poor coding does not mean Postgres is obligated to do so. As the link by @a_horse_with_no_name implies converting code is easy, once you migrate how you think about the problem in the first place. The closest you can get is return a tuple with a 0 id. The following is one way.

create or replace function public.get_some_data(
    p_id integer)
    returns table ( id integer, name character varying )
    language plpgsql
as $$
declare 
    v_at_least_one boolean = false; 
    v_exp_rec record;
begin
    for v_exp_rec in  
        select a.id, a.name
          from public.exampletable a
        where a.id = p_id   
        union all
        select 0,null
    loop
        if v_exp_rec.id::integer > 0
        or (v_exp_rec.id::integer = 0 and not v_at_least_one)
        then 
            id = v_exp_rec.id;
            name = v_exp_rec.name;
            return next;
            v_at_least_one = true;
        end if;
    end loop ;    
    return;
end 
$$;

But that is still just a hack and assumes there in not valid row with id=0. A much better approach would by for the calling routing to check what the function returns (it has to do that in one way or another anyway) and let the function just return the data found instead of making up data. That is that mindset shift. Doing that you can reduce this function to a simple select statement:

create or replace function public.get_some_data2(
    p_id integer)
    returns table ( id integer, name character varying )
    language sql strict
as $$
   select a.id, a.name
     from public.exampletable a
    where a.id = p_id;   
$$;  

Or one of the other solutions offered.

Answer 2

If you just want to return a set of a single table, using returns setof some_table is indeed the easiest way. The most basic SQL function to do that would be:

create function get_data()
   returns setof some_table
as
$$
  select * 
  from some_table;
$$
language sql;

PL/pgSQL isn't really necessary to put a SELECT statement into a function, but if you need to do other things, you need to use RETURN QUERY in a PL/pgSQL function:

create function get_data()
   returns setof some_table
as
$$
begin
  return query
    select * 
    from some_table;
end;
$$
language plpgsql;

---

A function as exactly one return type. You can't have a function that sometimes returns an integer and sometimes returns thousands of rows with a dozen columns.

The only thing you could do, if you insist on returning something is something like this:

create function get_data()
   returns setof some_table
as
$$
begin
  return query
    select * 
    from some_table;

  if not found then  
    return query 
      select (null::some_table).*;
  end if;
end;
$$
language plpgsql;

But I would consider the above an extremely ugly and confusing (not to say stupid) solution. I certainly wouldn't let that pass through a code review.

---

The caller of the function can test if something was returned in the same way I implemented that ugly hack: check the found variable after using the function.