temp-Tabelle in PL/pgSQL Funktion

Hallo,

ich bin relativ neu bei Postgres und muss bzw. möchte jetzt eine Funktion schreiben, die eine temp-Tabelle erzeugt, füllt und den Inhalt (mit select *) als Resultset zurückliefert. Das man eine statische Tabelle als Return-Typ einer Funktion angeben kann, weiss ich mittlerweile. Aber was ist mit temp-Tabellen ? Hier mal ein Beispiel was ich mir so vorgestellt habe bzw. was ich bisher probiert habe :

CREATE FUNCTION … (m_type character varying(40)) RETURNS setof record AS $$
DECLARE

BEGIN

drop TABLE if exists logger_data;
CREATE TEMP TABLE logger_data (
dataDate timestamp without time zone,

);

insert into logger_data
(dataDate)
select …
from …
where …

update logger_data
set dataDate …
from …
where …

select * into rec from logger_data order by dataDate;
return rec;

END;
$$ LANGUAGE plpgsql;

Allerdings bekomme ich beim Aufruf dieser Funktion immer die Fehlermeldung “eine Spaltendefinitionsliste ist erforderlich bei Funktionen, die »record« zurückgeben”. Leider habe ich in der Online-Doku dazu bisher nichts gefunden.

Alternativ habe ich mal versucht das Beispiel unter ONJava.com – Stored Procedures for Java Programmers zum Laufen zu bekommen, allerdings bekomme ich irgendwie immer kein Resultset zurück :wink:

Vielen Dank schonmal
Lothar

Deine variable rev ist erst einmal nur eine undefinierte Variable, und diese willst Du als Return-Wert zurückgeben. PG kann an dieser Stelle nicht wissen, was da an Spalten kommen wird.

Abhilfe: deklariere bitte die zu selektierenden Spalten, ein select * ist eh böse.

Weiterhin: du solltest DDL-Befehle wie CREATE TABLE in proceduren als EXECUTE aufrufen, sonst bekommst Du Probleme, von denen Du jetzt noch gar nichts ahnst…

Andreas

Hallo Andreas,

das habe ich noch nicht so ganz verstanden:
Wenn ich jetzt

select dataDate,…
into rec
from logger_data
order by dataDate;

mache, kommt der gleiche Fehler immer noch. Wie müssen denn die zu selektierenden Spalten deklariert werden ?

Lothar

Ich zeig Dir mal ein Bleistift:

test=# select * from foo;
 id |  t   | o
----+------+---
    | foo  | 3
    | bar  | 1
    | batz | 2
(3 rows)

test=*# create or replace function foo_f() returns setof record as $$ declare r record; begin for r in select * from foo loop return next r; end loop; return; end; $$ language plpgsql;
CREATE FUNCTION
test=*# select * from foo_f() as (i int, t text, o int);
 i |  t   | o
---+------+---
   | foo  | 3
   | bar  | 1
   | batz | 2
(3 rows)

test=*# select * from foo_f();
ERROR:  a column definition list is required for functions returning "record"

Der Versuch vor dem Fehler ist erfolgreich, ich hoffe, das hilft. Ansonsten, besser lesbar und schöner, sind IN/OUT-Parameter:

test=# create or replace function foo_f(OUT _id int, OUT _t text, OUT _o int) returns setof record as $$ declare r record; begin for r in select id,t,o from foo loop _id:=r.id;_t:=r.t;_o:=r.o; return next ; end loop; return; end; $$ language plpgsql;
CREATE FUNCTION
test=*# select * from foo_f();
 _id |  _t  | _o
-----+------+----
     | foo  |  3
     | bar  |  1
     | batz |  2
(3 rows)

aufpassen sollte man dabei, was Variablen und was Spaltenbezeichner sind. Ich habe den Variablen daher einen _ vorangestellt.


Andreas

Hallo Andreas,

vielen Dank für Deinen Tip. Ich hatte leider noch keine Zeit das auszuprobieren, da ich noch ein anderes merkwürdiges Problem habe:

In der Funktion habe eine temp-Tabelle wie folgt definiert :

drop TABLE if exists logger_serial;
CREATE TEMP TABLE logger_serial (
id bigint,
loggerid bigint,
loggernumber character varying(30)
);

Und eine sequence, da ich diese als Zähler benutzen will:

drop sequence if exists logger_serial_sequence;
create temp sequence logger_serial_sequence;

Die Tabelle wird wie folgt gefüllt :

insert into logger_serial
(id,loggerid,loggernumber)
select nextval(‘logger_serial_sequence’),id,serialnumber
from dataLogger
where serialnumber like ‘%611194%’ or
serialnumber like ‘%611295%’ ;

Was ich jetzt nicht verstehe, obwohl die Funktion fehlerfrei ausgeführt wird (mit psql), ist die Tabelle immer leer. Wenn ich diese Statements separat im pgAdmin ausführe, funktioniert alles einwandfrei. Ich habe im select extra auf Gross- und Kleinschreibung geachtet. Das Problem tritt aber scheinbar sowohl unter Windows als auch unter Linux auf. Irgendwie bin ich ratlos… Kann das mit den Problemen zusammenhängen, die Du vorhin angedeutet hast ?

Lothar

Hallo nochmal,

ich habe jetzt eine Lösung gefunden, allerdings wirkt das auf mch recht seltsam. Ich musste in der Query zwingend einen Alias vergeben und für alle Spalten benutzen. Dann läuft es :wink:

insert into logger_serial
(id,loggerid,loggernumber)
select nextval(‘logger_serial_sequence’),d.id,d.serialnumber
from datalogger d
where d.serialnumber like ‘%611194%’ or
d.serialnumber like ‘%611295%’ ;

Lothar

Groß/Kleinschreibung ist bei PG, im Kontrast zu anderen Systemen, nicht vom OS abhängig. Das, was ich vorhin andeuten wollte, war was anderes: wenn Du innerhalb einer Function DDL-Befehle, also CREATE TABLE oder sowas nutzt, cached PG die OID dieser Objekte. Bei erneutem Aufruf gibt es dann Probleme. Das ist seit 8.3 gelöst (plan invalidation, als Google-Reizwort).

Andreas

Hallo Andreas,

ich habe doch noch eine (hoffentlich) letzte Frage: Prinzipiell funktioniert das jetzt alles soweit ganz gut, aber immer nur einmal. D.h., wenn ich die Funktion mit create erzeuge und dann mit select * from getTrinidadMeasurements () aufrufe, bekomme ich das erwartete Ergebniss zurück. Wenn ich jetzt die Funktion aber ein zweites mal aufrufe, behauptet Postgres, dass meine temp-Tabelle nicht vorhanden wäre (zumindest glaube ich das), obwohl die doch eigentlich in der Funktion erzeugt wird :

ERROR: relation with OID 320612 does not exist
SQL Status:42P01
Kontext:SQL statement "INSERT INTO logger_serial …
PL/pgSQL function “gettrinidadmeasurements” line 43 at SQL statement

Die Funktion sieht ungefähr so aus:

drop FUNCTION if exists getTrinidadMeasurements …

CREATE FUNCTION getTrinidadMeasurements …

drop TABLE if exists logger_serial;
CREATE TEMP TABLE logger_serial (
id bigint,
loggerid bigint,
loggernumber character varying(30),
blocknumber character varying(50)
);

insert into logger_serial
(id,loggerid,loggernumber,blocknumber)
select …

Ich bin mal wieder ratlos :wink: . Wenn ich die Funktion wieder neu erzeuge, funktioniert der Zugriff wieder genau 1x und ich bin wieder am Anfang.

Lothar

Ich zitiere mich mal selber:

Weiterhin: du solltest DDL-Befehle wie CREATE TABLE in proceduren als EXECUTE aufrufen, sonst bekommst Du Probleme, von denen Du jetzt noch gar nichts ahnst…

Womit nun auch geklärt wäre, auf was ich da angespielt habe.

Andreas

Hallo Andreas,

prinzipiell habe ich das jetzt zwar verstanden, allerdings tritt der Fehler immer noch, obwohl ich jetzt alle DDL-Befehle mit EXECUTE ausführe. Muss ich das insert selbst auch mit EXECUTE ausführen ?

Lothar

Ja, stimmt, ist wohl nötig.

Andreas

Alles klar. Jetzt läufts. Ich mußte bloß noch alle Zugriffe auf die temp-Tabelle ebenfalls mit execute ausführe. Ist halt ziemlich umständlich. Bei Gelegenheit werde ich mir mal 8.3 angucken.

Lothar

In 8.3 wird sich dein Problem wohl nicht in Luft aufgelöst haben.

Die Ursache ist folgende: wenn du den SQL-Code statisch in die Funktion schreibst, setzt PostgreSQL diesen Code beim Schreiben der Funktion sofort in die zugehörigen OIDs und Datenbankinhalte um, damit das Ausführen schneller ist.

Wenn du diese Funktion dann jedoch später aufrufst, gibt es diese Werte womöglich gar nicht.

Aus dem Grund musst du die Queries dynamisch generieren, damit sichergestellt ist, dass das Parsen erst zur Laufzeit stattfindet und nicht bereits beim Erstellen der Funktion.

Irre ich mich, oder wurde PLAN INVALIDATION (guggst Du Merlin’s stuff: PostgreSQL 8.3 Features: Plan Invalidation ) nicht gerade dafür in 8.3 gemacht?

Andreas

Ich nehme alles zurück und behaupte das Gegenteil :wink:

Nein, keine Ahnung, muss ich mir anschauen.

Todo für mein Buch: PLAN INVALIDATION