Function Returning a Table / View

Fragen und Probleme zu PL/pgSQL.
Antworten
annewhut
Neuer Benutzer
Beiträge: 2
Registriert: Mi 29. Mai 2019, 10:18

Function Returning a Table / View

Beitrag von annewhut » Mi 29. Mai 2019, 10:28

I have a function that returns a table but I need it to be a view, is it possible?

here's the code:

Code: Alles auswählen

-- Function: notes_to_financial_statements_for_the_month(character varying)

-- DROP FUNCTION notes_to_financial_statements_for_the_month(character varying);

CREATE OR REPLACE FUNCTION notes_to_financial_statements_for_the_month(IN _year_month character varying)
  RETURNS TABLE(account_code character varying, amount numeric) AS
$BODY$
DECLARE
	
BEGIN	
	RETURN QUERY SELECT x.account_code,
	--coa.acct_title,
	--coa.level,
	CASE WHEN x.account_code ~~ '1%' THEN
		SUM(x.debit) - SUM(x.credit)
	ELSE SUM(x.credit) - SUM(x.debit) END AS amount
	FROM
		(
		select gl4_account_code AS account_code,
		CASE WHEN gl4_entry_type = 'D' THEN
			SUM(gl4_amount)
		ELSE 0 END AS debit,
		CASE WHEN gl4_entry_type = 'C' THEN
			SUM(gl4_amount)
		ELSE 0 END AS credit,
		gl4_entry_type
		from coop_fin_gl_level4
		where gl4_posting_date::text ~ _year_month
		and (gl4_account_code ~~ '1%'
		or gl4_account_code ~~ '2%'
		or gl4_account_code ~~ '3%')
		group by gl4_account_code,
		gl4_entry_type

	UNION ALL

		select gl5_account_code AS account_code,
		CASE WHEN gl5_entry_type = 'D' THEN
			SUM(gl5_amount)
		ELSE 0 END AS debit,
		CASE WHEN gl5_entry_type = 'C' THEN
			SUM(gl5_amount)
		ELSE 0 END AS credit,
		gl5_entry_type
		from coop_fin_gl_level5
		where gl5_posting_date::text ~ _year_month
		and (gl5_account_code ~~ '1%'
		or gl5_account_code ~~ '2%'
		or gl5_account_code ~~ '3%')
		group by gl5_account_code,
		gl5_entry_type
		) x
	JOIN coop_fin_chart_of_accounts coa
	ON coa.account_code = x.account_code
	GROUP BY x.account_code
	ORDER BY x.account_code;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION notes_to_financial_statements_for_the_month(character varying)
  OWNER TO postgres;



akretschmer
Erfahrener Benutzer
Beiträge: 7640
Registriert: So 29. Okt 2006, 13:35
Wohnort: Kaufbach, Sachsen, Germany, Germany
Kontaktdaten:

Re: Function Returning a Table / View

Beitrag von akretschmer » Mi 29. Mai 2019, 10:34

you can't pass parameters to a view, but your function need parameters.

Btw, this is a german speaking forum, can we switch to german?
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

annewhut
Neuer Benutzer
Beiträge: 2
Registriert: Mi 29. Mai 2019, 10:18

Re: Function Returning a Table / View

Beitrag von annewhut » Mi 29. Mai 2019, 11:05

Ich habe dort einen Parameter in meiner Funktion, nämlich

(IN _year_month character varying)

Es tut mir leid, ich wechsle jetzt zu Deutsch

akretschmer
Erfahrener Benutzer
Beiträge: 7640
Registriert: So 29. Okt 2006, 13:35
Wohnort: Kaufbach, Sachsen, Germany, Germany
Kontaktdaten:

Re: Function Returning a Table / View

Beitrag von akretschmer » Mi 29. Mai 2019, 11:27

annewhut hat geschrieben:
Mi 29. Mai 2019, 11:05
Ich habe dort einen Parameter in meiner Funktion, nämlich

(IN _year_month character varying)

ja, hab ich gesehen. Warum benötigst Du das als VIEW?
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

pogomips
Erfahrener Benutzer
Beiträge: 105
Registriert: Fr 18. Nov 2016, 09:55

Re: Function Returning a Table / View

Beitrag von pogomips » Mi 29. Mai 2019, 23:06

Deine Function könnte sicher etwas Optimierung vertragen oder?

Aber zur Frage:
a)
Ich denke man kann auch Functions im View nutzen. Man muss nur über die Datenquelle dafür sorgen, dass geeignete Zeilenwerte (1 Zeile) gefiltert werden und als Functionparameter übergeben werden.
Dazu müsste allerdings sichergestellt werden, dass der Optimizer erst die Datenmenge filtert und dann die Function aufruft. Ich habs grad mal probiert, aber allein über hohe Kosten bei der Function lässt sich der Optimizer nicht bewegen das zu tun. Keine Ahnung, ob er das überhaupt so macht. Aber hier sind ja Experten dabei, die das sicher wissen.

b)
diese gezeigte Function macht auch nichts anderes als ein Select, das kann man auch genausogut gleich als View machen. Ein ungefilterter Aufruf könnte dann natürlich den Server sehr quälen, aber das kann man ja verhindern.

zu b)
in einem DB System der Konkurrenz gibt es Session Variables, die eigenen sich prima um einen View "von innen " zu filtern. Setzt man den Parameter auf den gewünschten Wert, bekommt man immer nur die Filtermenge beim Öffnen des Views, vergißt man den Parameter zu setzen (session variable), ergibt das eine leer Menge beim Öffnen des Views. Postgres hat sowas ähnliches und es ist etwas umständlicher und unflexibler. Ich meine man muss irgendwelche Variablen deklarieren oder sowas. Ich hab aber im Hinterkopf, dass es mittlerweile irgendeine Extension gibt, die session variables nachbaut. Wissen die Experten hier sicher auch.
Das Ganze taugt allerdings auch nicht so dolle, wenn es gar keine ständigen Sessions gibt, also klassischerweise im normalen App Server / Webserver Betrieb.

Antworten