jeudi 25 février 2016

PostgreSQL - Update one column with values from other columns of the same table

I am trying to write a function that will update a column with values that are stored in one of several columns of the same table. The feeding columns represent a taxonomic hierarchy that usually is not complete so I want the receiving column to store the least high of the available values. This Is the closest I got and I sense that I am still far away :).

CREATE OR REPLACE FUNCTION get_taxon() RETURNS void LANGUAGE plpgsql ASBEGIN $BODY$ BEGIN IF taxon.subespecie is not null THEN UPDATE taxon SET taxon = subespecie FROM taxon; ELSIF taxon.especie is not null THEN UPDATE taxon SET taxon = especie; ELSIF taxon.genero is not null THEN UPDATE taxon SET taxon = genero; ELSIF taxon.subfamilia is not null THEN UPDATE taxon SET taxon = subfamilia; ELSIF taxon.familia is not null THEN UPDATE taxon SET taxon = familia; ELSIF taxon.infraordem is not null THEN UPDATE taxon SET taxon = infraordem; ELSIF taxon.subordem is not null THEN UPDATE taxon SET taxon = subordem; END IF; END; $BODY$

when I call the function I get this error:

ERROR: missing FROM-clause entry for table "taxon" LINE 1: SELECT taxon.subespecie is not null

Appart from this I also have a general doubt: what kind of `RETURN' should be declared for a function like this, that updates existing fields?

Aucun commentaire:

Enregistrer un commentaire