Return a Query from a Function

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 a query from a function?

CREATE OR REPLACE FUNCTION get_names(_tname varchar)
  RETURNS TABLE (col_a integer, col_b text) AS
$func$
BEGIN
   RETURN QUERY
   SELECT t.col_a, t.col_b  -- must match RETURNS TABLE
   FROM   mytable t
   WHERE  t.name = _tname;    
END
$func$  LANGUAGE plpgsql;

Call like this:

SELECT * FROM get_names('name')

Major points:

• Use RETURNS TABLE, so you don't have to provide a list of column names with every call.

• Use RETURN QUERY, much simpler.

• Table-qualify column names to avoid naming conflicts with identically named OUT parameters (including columns declared with RETURNS TABLE).

• Use a named variable instead of ALIAS. Simpler, doing the same, and it's the preferred way.

• A simple function like this could also be written in LANGUAGE sql:

CREATE OR REPLACE FUNCTION get_names(_tname varchar)
  RETURNS TABLE (col_a integer, col_b text) AS
$func$
SELECT t.col_a, t.col_b  --, more columns - must match RETURNS above
FROM   mytable t
WHERE  t.name = $1;
$func$ LANGUAGE sql;

How can I return the result of a select in a function in MySQL?

A MySQL function can only return a single value. You can use a stored procedure, instead:

DELIMITER $$

CREATE PROCEDURE `getCharacters`() 
BEGIN

        SELECT * 
        FROM `characters` 
        WHERE `level` > 50;

END $$

DELIMITER ;

CALL getCharacters;

More info about the difference between stored procedures and functions can be found here: MySQL stored procedure vs function, which would I use when?

How would I return data in a query function

instead of forEach you can use map to return an array of promises and then use Promise.all to resolve those

const resultPromises = testNames.map(function(name){

    return query("TIME_SERIES_INTRADAY", name)

    .then(response => response.json())

}

Promise.all(resultPromises).then(results=>console.log(results))

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;

User function return query and insert into

No need for plpgsql and no need for two queries either. Combine the INSERT and SELECT, and use RETURNING.

CREATE OR REPLACE FUNCTION my_func(taskID INTEGER)
RETURNS INTEGER
LANGUAGE SQL
AS
$$
    INSERT INTO table_bar(job_id)
    SELECT  id
    FROM    table_foo
    WHERE   task_id = taskID
    RETURNING id;
$$;

How to return a request function from useQuery in react-query

Digging in docs, I find out that React-Query in useQuery hook provide a refetch() function.

In my case, I just set property enabled to false (just so that the function when mount is not called automatically), and just return a request-function like this

export const useGetFakeData = () => {
  const { refetch } = useQuery<void, Error, any>({
    queryFn: () =>
      fetch('https://fake-domain.com').then(data => console.log('Data arrived: ', data)),
    queryKey: 'fake-data',
    enabled: false,
  })

  return refetch
}

---

Related Topics