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

Is this a function or a procedure? What language are you even using, SQL or PL/PGSQL? Can you show us your code, please? — Bergi, Apr 17 '20 at 14:11
I cant really show my code. But, migration is from SQL Server Procedure to POstgrSQL Function. And language I am using is pgplsql. If you want, I can write a short program to make you understand, what I am trying to ask. — sys, Apr 17 '20 at 14:13
It might help, yes, if you're not satisfied with the nameless horse's answer below. — Bergi, Apr 17 '20 at 14:20
You cannot return the number `0` if your return type is multiple rows. — Bergi, Apr 17 '20 at 14:21
yea..thats what ! I know that, just checking, if any kind of workaround. I know its not the way PostgrSQL Works. — sys, Apr 17 '20 at 14:33

score 2 · Answer 1 · 2020-04-17T14:28:04.893

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.

edited Apr 17 '20 at 14:28

answered Apr 17 '20 at 14:16

Naah ! This is fine. What I want is something like - I edited above the main query. Thanks for help, anyway. – sys Apr 17 '20 at 14:18
If I cannot find the record , based on the input parameter, I simply want to return a Zero., not null, not empty,, a single digit zero. Thats the problem, .We can do this in a SQL Server Procedure, or plsql procedure by a select statement, but here in Postgresql, its getting difficult. Otherwise, its simple, return query, setof, etc etc. – sys Apr 17 '20 at 14:23
@SayanB: you simply can't. A function as exactly **one** return type. You can't just change that return type on the fly. You can return a dummy row with null values but that would be an extremely strange thing to do (I would certainly not let something like that pass in a code review). Why would you need that anyway? If nothing is returned the caller will know that. – Apr 17 '20 at 14:25
The question is not 'why'. I was asking, is there a work around for this. Is there a other way to put a kind of dynamic Return type , in PostgreSQL. I also, know its not possible, was checking, if someone has done that.. – sys Apr 17 '20 at 14:28
@SayanB: the answer is simply no, you can not do that. I would consider a solution that does that, an ugly hack, and it wouldn't pass a code review I conduct for an Oracle procedure either. Stuff like that is much better done by the caller. Database migration is not only about code rewriting, you need to [migrate your mindset to](https://blog.sql-workbench.eu/post/migrate-your-mindset/). This is imply **not** how things are done in Postgres – Apr 17 '20 at 14:30

score 0 · Answer 2 · answered Apr 17 '20 at 22:06

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.

Query on Return Statement - PostgreSQL

2 Answers2