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

I have this function in PostgreSQL, but I don't know how to return the result of the query:

CREATE OR REPLACE FUNCTION wordFrequency(maxTokens INTEGER)
  RETURNS SETOF RECORD AS
$$
BEGIN
    SELECT text, count(*), 100 / maxTokens * count(*)
    FROM (
        SELECT text
    FROM token
    WHERE chartype = 'ALPHABETIC'
    LIMIT maxTokens
    ) as tokens
    GROUP BY text
    ORDER BY count DESC
END
$$
LANGUAGE plpgsql;

But I don't know how to return the result of the query inside the PostgreSQL function.

I found that the return type should be SETOF RECORD, right? But the return command is not right.

What is the right way to do this?

This question is related to sql postgresql return plpgsql return-type

The answer is


Hi please check the below link

https://www.postgresql.org/docs/current/xfunc-sql.html

EX:

CREATE FUNCTION sum_n_product_with_tab (x int)
RETURNS TABLE(sum int, product int) AS $$
    SELECT $1 + tab.y, $1 * tab.y FROM tab;
$$ LANGUAGE SQL;

Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to postgresql

Subtracting 1 day from a timestamp date pgadmin4 : postgresql application server could not be contacted. Psql could not connect to server: No such file or directory, 5432 error? How to persist data in a dockerized postgres database using volumes input file appears to be a text format dump. Please use psql Postgres: check if array field contains value? Add timestamp column with default NOW() for new rows only Can't connect to Postgresql on port 5432 How to insert current datetime in postgresql insert query Connecting to Postgresql in a docker container from outside

Examples related to return

Method Call Chaining; returning a pointer vs a reference? How does Python return multiple values from a function? Return multiple values from a function in swift Python Function to test ping Returning string from C function "Missing return statement" within if / for / while Difference between return 1, return 0, return -1 and exit? C# compiler error: "not all code paths return a value" How to return a struct from a function in C++? Print raw string from variable? (not getting the answers)

Examples related to plpgsql

No function matches the given name and argument types Postgres FOR LOOP Return zero if no record is found PostgreSQL - SQL state: 42601 syntax error Store query result in a variable using in PL/pgSQL PL/pgSQL checking if a row exists PostgreSQL IF statement Postgresql, update if row with some unique value exists, else insert Loop over array dimension in plpgsql How to return result of a SELECT inside a function in PostgreSQL?

Examples related to return-type

How to specify multiple return types using type-hints Return from lambda forEach() in java Return different type of data from a method in java? Apply pandas function to column to create multiple new columns? C++: variable 'std::ifstream ifs' has initializer but incomplete type Returning anonymous type in C# How do I make the return type of a method generic? How to return result of a SELECT inside a function in PostgreSQL? Can two Java methods have same name with different return types? What should main() return in C and C++?