Problems with a functions definition in PostgreSQL

[Sten-Erik Björling]

Dear all,

Many thanks for your solution tips and explanations - below the code that works as an example…

CREATE OR REPLACE FUNCTION
 fn_actor_sales_by_film_category (INT)
 RETURNS TABLE (r_category VARCHAR, r_total_sum NUMERIC)
 AS
 $$
 BEGIN
 RETURN QUERY
 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 =  $1 BY c.name
 ORDER BY (sum(p.amount)) DESC;
 END
 $$
 LANGUAGE plpgsql;

Take care, all the best…

This code is used when testing performance - a separate subject on the list...

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.