How to Return Result of a Select Inside a Function in Postgresql

How to return result of a SELECT inside a function in PostgreSQL?

Use RETURN QUERY:

CREATE OR REPLACE FUNCTION word_frequency(_max_tokens int)
  RETURNS TABLE (txt   text   -- also visible as OUT param in function body
               , cnt   bigint
               , ratio bigint)
  LANGUAGE plpgsql AS
$func$
BEGIN
   RETURN QUERY
   SELECT t.txt
        , count(*) AS cnt                 -- column alias only visible in this query
        , (count(*) * 100) / _max_tokens  -- I added parentheses
   FROM  (
      SELECT t.txt
      FROM   token t
      WHERE  t.chartype = 'ALPHABETIC'
      LIMIT  _max_tokens
      ) t
   GROUP  BY t.txt
   ORDER  BY cnt DESC;                    -- potential ambiguity 
END
$func$;

Call:

SELECT * FROM word_frequency(123);

Defining the return type explicitly is much more practical than returning a generic record. This way you don't have to provide a column definition list with every function call. RETURNS TABLE is one way to do that. There are others. Data types of OUT parameters have to match exactly what is returned by the query.

Choose names for OUT parameters carefully. They are visible in the function body almost anywhere. Table-qualify columns of the same name to avoid conflicts or unexpected results. I did that for all columns in my example.

But note the potential naming conflict between the OUT parameter cnt and the column alias of the same name. In this particular case (RETURN QUERY SELECT ...) Postgres uses the column alias over the OUT parameter either way. This can be ambiguous in other contexts, though. There are various ways to avoid any confusion:

1. Use the ordinal position of the item in the SELECT list: ORDER BY 2 DESC. Example:
   
   • Select first row in each GROUP BY group?
2. Repeat the expression ORDER BY count(*).
3. (Not required here.) Set the configuration parameter plpgsql.variable_conflict or use the special command #variable_conflict error | use_variable | use_column in the function. See:
   
   • Naming conflict between function parameter and result of JOIN with USING clause

Don't use "text" or "count" as column names. Both are legal to use in Postgres, but "count" is a reserved word in standard SQL and a basic function name and "text" is a basic data type. Can lead to confusing errors. I use txt and cnt in my examples, you may want more explicit names.

Added a missing ; and corrected a syntax error in the header. (_max_tokens int), not (int maxTokens) - data type after name.

While working with integer division, it's better to multiply first and divide later, to minimize the rounding error. Or work with numeric or a floating point type. See below.

Alternative

This is what I think your query should actually look like (calculating a relative share per token):

CREATE OR REPLACE FUNCTION word_frequency(_max_tokens int)
  RETURNS TABLE (txt            text
               , abs_cnt        bigint
               , relative_share numeric)
  LANGUAGE plpgsql AS
$func$
BEGIN
   RETURN QUERY
   SELECT t.txt, t.cnt
        , round((t.cnt * 100) / (sum(t.cnt) OVER ()), 2)  -- AS relative_share
   FROM  (
      SELECT t.txt, count(*) AS cnt
      FROM   token t
      WHERE  t.chartype = 'ALPHABETIC'
      GROUP  BY t.txt
      ORDER  BY cnt DESC
      LIMIT  _max_tokens
      ) t
   ORDER  BY t.cnt DESC;
END
$func$;

The expression sum(t.cnt) OVER () is a window function. You could use a CTE instead of the subquery. Pretty, but a subquery is typically cheaper in simple cases like this one (mostly before Postgres 12).

A final explicit RETURN statement is not required (but allowed) when working with OUT parameters or RETURNS TABLE (which makes implicit use of OUT parameters).

round() with two parameters only works for numeric types. count() in the subquery produces a bigint result and a sum() over this bigint produces a numeric result, thus we deal with a numeric number automatically and everything just falls into place.

Return select * result in postgres function

This can be done with a simple SQL function:

CREATE OR REPLACE FUNCTION get_data(v1 integer)
RETURNS setof part_movement 
AS 
$$
   Select * 
   From part_movement 
   where id_area_final_destination = v1;
$$ LANGUAGE sql;

More details and examples can b found in the manual:

http://www.postgresql.org/docs/current/static/xfunc-sql.html#XFUNC-SQL-FUNCTIONS-RETURNING-SET

Return result of query from function in postgresql

Try this:

CREATE OR REPLACE FUNCTION get_all_record(OUT id integer, OUT field2 varchar(20))
RETURNS SETOF record
AS $$
BEGIN
    RETURN QUERY SELECT table1.id, table1.field2 FROM tiers;
END;
$$
LANGUAGE plpgsql;

How to use CASE in postgres function to return Select statement?

How can you specify columns if I have different tables to return depending on function argument? You cannot. A function must always have the same return type and the exact return must be declared in the function definition.

You can sort-of get what you are after by having your function return a refcursor; see Cursors 43.7.3.5 (or the appropriate section for you Postgres version) Something like:

create or replace function confused_function( What_to_do integer) 
  returns refcursor
 language plpgsql
as $$
declare 
  rec  refcursor; 
begin 
    case when What_to_do  = 1 then 
              open rec for select * from t1; 
             
         when What_to_do  = 2 then
              open rec for select * from t2; 
         
        else 
             raise exception 'Invalid What_to_do parameter value specified (%)', What_to_do
                using Hint = 'Value for Parameter: What_to_do must be 1 or 2'; 
    end case; 

    return rec; 
end;
$$;

This however transfers the cursor processing to the calling routine. You would be better off writing 2 functions (one for each SQL). Let the calling routine analyze the 'parameter' and invoke to appropriate function (or just run the SQL without an intervening function).



A little development philosophy: A function should do one thing, do it well, and know everything it needs accomplish it. Your function does not know what to do; it needs to be told.

PostgreSQL function to return a join from 2 select statements

1. You should use the plpgsql language, not sql
2. Write RETURN QUERY before every SELECT
3. Don't forget putting a semicolon at the end of each statement
4. Read the manual :)

how to get the result of the select query and pass it as a input to delete query in Function Postgresql

First store value in a variable for given input and these execute delete command with this variable and return this variable.

-- PostgreSQL
-- create table and store data
create table employee (emp_id int, name varchar(255));
insert into employee values (1, 'Rahul'), (2, 'Biswas'), (3, 'Shuva');

-- create function
CREATE OR REPLACE FUNCTION test(p_name varchar(255))

RETURNS INTEGER AS

$BODY$

DECLARE v_emp_id INTEGER;

BEGIN
 SELECT e.emp_id into v_emp_id from employee e where e.name = p_name;
 
 DELETE FROM employee WHERE emp_id = v_emp_id;

 return v_emp_id;

END

$BODY$

LANGUAGE plpgsql;

-- execute function
select * from test('Rahul');

Please check this url https://dbfiddle.uk/?rdbms=postgres_11&fiddle=4c134787dafd8fe58b032d700168318d

---

Related Topics