Problems with a functions definition in PostgreSQL

[dlr at futurechalk.com]

Hello Sten

I think you can only have one returned argument with SETOF. That argument can contain multiple rows and columns. 

Sort of like this,

CREATE FUNCTION getfoo(int) RETURNS SETOF foo AS $$
    SELECT * FROM foo WHERE fooid = $1;
$$ LANGUAGE SQL;

SELECT * FROM getfoo(1) AS t1;
 fooid | foosubid | fooname
-------+----------+---------
     1 |        1 | Joe
     1 |        2 | Ed
(2 rows)



> On Jan 2, 2023, at 10:14 AM, Sten-Erik Björling <s-e.bjorling at enviro.se> wrote:
> 
> Hi all,
> 
> Have a problem with defining a function in Postgres. The SQL that is the skeleton of the function is OK - the problem is the definition of the result - a list containing the genres and their aggregated incomes. I set the RETURN to use SETOF.
> 
> CREATE OR REPLACE FUNCTION fn1_sales_by_film_category (x INT) RETURNS SETOF category, total_sales AS $$  SELECT c.name AS category, sum(p.amount) AS total_sales
> FROM payment p
> JOIN rental r ON p.rental_id = r.rental_id
> JOIN inventory i ON r.inventory_id = i.inventory_id
> JOIN film f ON i.film_id = f.film_id
> JOIN film_actor fa ON fa.film_id = f.film_id
> JOIN actor a ON fa.actor_id = a.actor_id
> JOIN film_category fc ON f.film_id = fc.film_id
> JOIN category c ON fc.category_id = c.category_id
> WHERE a.actor_id = x GROUP BY c.name
> ORDER BY (sum(p.amount)) DESC $$
> LANGUAGE SQL;
> 
> Thanks in advance…
> 
> Take care, all the best…
> 
> Stene
> 
> ______
> 
> Sten-Erik Björling
> Enviro Data
> Kyrkogatan 5A 2 tr
> SE-972 32  Luleå
> Sweden
> 
> E-Mail: s-e.bjorling at enviro.se
> Mobile: +46-70-655 11 72
> Wire: @stenerikbjorling
> Skype: stenerikbjorling
> iChat: stene at icloud.com
> Signal: +46 70 655 11 72
> FaceTime: stene at icloud.com
> Telegram: @stenerikbjorling
> Hotmail / Messenger: stenerikbjorling at hotmail.com
> GMail: stenerikbjorling at gmail.com
> ______
> 
> This email and any files transmitted with it are confidential, may be legally privileged and are intended solely for the use of the individual or entity to whom they are addressed. If you are not the intended recipient, please note that any use, distribution, or reproduction of the contents of this email is strictly prohibited and may be unlawful. If you are not the intended recipient, please notify the sender by return email and destroy all copies of the original message including any attachments thereto. Thank you.
> 
> Please note that we take reasonable precautions to prevent the transmission of viruses; however, we cannot guarantee that this email or its attachments are free from viruses. We only send and receive emails on the basis that we are not be liable for any loss or damage resulting from the opening of this message and/or attachments.
> 
> _____________________________________________________________
> Manage your list subscriptions at https://lists.omnis-dev.com
> Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com