PostgreSQL: FEHLER: 42601: eine Spalten-Definitionsliste ist für Funktionen erforderlich, die "record" zurückgeben

(Disclaimer: PostgreSQL newbie.)

OK, soweit ich das beurteilen kann, meine Funktion richtig ähnelt den mustern, die ich gesehen habe. Kann jemand Ahnung, mir in wie bekomme ich diese zu arbeiten?

create or replace function get_user_by_username(
    username varchar(250),
    online boolean
    ) returns setof record as $$
declare result record;
begin

    if online then 
        update users
        set last_activity = current_timestamp
        where user_name = username;
    end if;

    return query
    select
        user_id,
        user_name,
        last_activity,
        created,
        email,
        approved,
        last_lockout,
        last_login,
        last_password_changed,
        password_question,
        comment
    from
        users
    where
        user_name = username
    limit 1;

    return;
end;
$$ language plpgsql;

InformationsquelleAutor der Frage Jeremy Holovacs | 2011-12-22

  1. 20

    wenn Sie möchten, create function returning setof record, müssen Sie definieren, Spalten-Typen, die in Ihrer select-Anweisung

    Mehr info

    Ihre Abfrage sollte wie folgt Aussehen:

    select * from get_user_by_username('Username', True) as 
  f(user_id integer, user_name varchar, last_activity, varchar, created date, email        archar, approved boolean, last_lockout timestamp, last_login timestamp, 
  last_password_changed timestamp, password_question varchar, comment varchar)

    (werden Sie wahrscheinlich benötigen, um die Daten zu ändern Arten)

    Ich persönlich bevorzuge die Typen Vorgehen. es versichert, dass, wenn die Funktion bearbeitet wird, werden alle Abfragen die richtigen Ergebnisse zurück. Könnte es sein, ein Schmerz, denn jedes mal, wenn Sie ändern Sie die Funktion, die die Argumente müssen Sie neu erstellen/drop-Typen aswell tho.

    ZB:

    CREATE TYPE return_type as 
(user_id integer,
 user_name varchar,
 last_activity varchar,
 created timestamp,
 email varchar,
 approved boolean,
 last_lockout timestamp ,
 last_login timestamp,
 last_password_changed timestamp,
 password_question varchar,
 comment varchar);

create or replace function get_user_by_username( username varchar(250), online 

boolean) returns setof return_type as $$
declare _rec return_type;
begin
    if online then 
        update users
        set last_activity = current_timestamp
        where user_name = username;
    end if;
    for _rec in select
        user_id,
        user_name,
        last_activity,
        created,
        email,
        approved,
        last_lockout,
        last_login,
        last_password_changed,
        password_question,
        comment
      from
        users
      where
        user_name = username
      limit 1 
    loop

      return next _rec;

    end loop

end;
$$ language plpgsql;

    InformationsquelleAutor der Antwort ertx

  2. 36

    Rückkehr ausgewählten Spalten

    CREATE OR REPLACE FUNCTION get_user_by_username(_username text, _online bool)
  RETURNS TABLE (
    user_id int
   ,user_name text
   ,last_activity timestamp
   , ... ) AS
$func$
BEGIN

IF _online THEN
   RETURN QUERY
   UPDATE users u 
   SET    last_activity = current_timestamp
   WHERE  u.user_name = _username
   RETURNING
          u.user_id
         ,u.user_name
         ,u.last_activity
         , ... ;
ELSE
   RETURN QUERY
   SELECT u.user_id
         ,u.user_name
         ,u.last_activity
         , ...
   FROM   users u
   WHERE  u.user_name = _username;
END IF;

END
$func$  LANGUAGE plpgsql;

    Nennen:

    SELECT * FROM get_user_by_username('myuser', TRUE)

    Wichtigsten Punkte

    • Sie hatte DECLARE result record; aber nicht mit der Variablen. Ich löschte die Reste.

    • Zurück können Sie den Datensatz direkt aus der UPDATE, die ist viel schneller als das aufrufen einer zusätzlichen SELECT - Anweisung. Verwenden RETURN QUERY und UPDATE mit GIBT - Klausel.

      Wenn der Benutzer nicht _online, standardmäßig eine Ebene SELECT.

    • Wenn Sie nicht Tisch-qualifizieren Spaltennamen (tablename.columnname) in Abfragen in der Funktion vorsichtig sein Namenskonflikte zwischen Spaltennamen und benannte Parameter, die sichtbar sind (die meisten) überall innerhalb einer Funktion.

      Sie können auch vermeiden, solche Konflikte durch die Verwendung von positions-Referenzen ($n) für die Parameter. Oder verwenden Sie ein Präfix ein, das Sie nie Verwendung für Spaltennamen: wie ein Unterstrich (_username).

    • Wenn users.username definiert ist einzigartige in der Tabelle, dann LIMIT 1 in der zweiten Abfrage nur Reste.

      Wenn es nicht, dann die UPDATE können mehrere Zeilen aktualisieren, die ist wahrscheinlich falsch.

      Ich nahm eine einzigartige username gelöscht und die Reste.

    • Definieren die Rückgabetyp der Funktion (wie @ertx gezeigt), oder Sie müssen, um eine definition für die Spalte Liste in jeder Funktion aufrufen, das ist umständlich.

    • Erstellen einen Typ für diesen Zweck (wie @ertx vorgeschlagen) ist ein Gültiger Ansatz, aber wahrscheinlich overkill für eine einzige Funktion. Das war der Weg zu gehen, die in alten Versionen von PostgreSQL vorher hatten wir ZURÜCKGIBT TABLE für diesen Zweck - wie oben gezeigt.

    • Du brauchen nicht eine Schleife für diese einfache Funktion.

    • Jede Funktion muss eine Sprache Erklärung. LANGUAGE plpgsql in diesem Fall.

    • Wohl keinen Punkt in der Definition einer Längenbeschränkung (varchar(250)) für den parameter. Ich vereinfacht zu geben text.

    Rückkehr gesamte Tabelle

    Wenn Sie zurückkehren möchten alle Spalten der Tabelle users gibt es eine einfachere Möglichkeit. PostgreSQL definiert automatisch eine composite-Typ mit dem gleichen Namen für jede Tabelle. In diesem Fall könnten Sie RETURNS SETOF users und erheblich vereinfachen die Abfrage:

    CREATE OR REPLACE FUNCTION get_user_by_username(_username text, _online bool)
  RETURNS SETOF users AS
$func$
BEGIN

IF _online THEN
    RETURN QUERY
    UPDATE users u 
    SET    last_activity = current_timestamp
    WHERE  u.user_name = _username
    RETURNING u.*;
ELSE
    RETURN QUERY
    SELECT *
    FROM   users u
    WHERE  u.user_name = _username;
END IF;

END
$func$  LANGUAGE plpgsql;

    Wenn Sie brauchen etwas mehr "Dynamik", die Sie berücksichtigen sollten:

    InformationsquelleAutor der Antwort Erwin Brandstetter

Schreibe einen Kommentar