Comment définir la valeur de la variable composée champ à l'aide de SQL dynamique
Compte tenu de ce type:
-- Just for testing purposes:
CREATE TYPE testType as (name text)
Je peux obtenir la valeur d'un champ dynamiquement avec cette fonction:
CREATE OR REPLACE FUNCTION get_field(object anyelement, field text) RETURNS text as
$BODY$
DECLARE
value text;
BEGIN
EXECUTE 'SELECT $1."' || field || '"'
USING object
INTO value;
return value;
END;
$BODY$
LANGUAGE plpgsql
Appel get_field('(david)'::testType, 'name')
fonctionne comme prévu, le retour "david".
Mais comment puis-je définir une valeur d'un champ dans un type composite? J'ai essayé ces fonctions:
CREATE OR REPLACE FUNCTION set_field_try1(object anyelement, field text, value text)
RETURNS anyelement
as
$BODY$
DECLARE
value text;
BEGIN
EXECUTE '$1."' || field || '" := $2'
USING object, value;
return object;
END;
$BODY$
LANGUAGE plpgsql
CREATE OR REPLACE FUNCTION set_field_try2(object anyelement, field text, value text)
RETURNS anyelement
as
$BODY$
DECLARE
value text;
BEGIN
EXECUTE 'SELECT $1 INTO $2."' || field || '"'
USING value, object;
return object;
END;
$BODY$
LANGUAGE plpgsql
CREATE OR REPLACE FUNCTION set_field_try3(object anyelement, field text, value text)
RETURNS anyelement
as
$BODY$
DECLARE
value text;
BEGIN
EXECUTE 'BEGIN $1."' || field || '" := $2; SELECT $1; END;'
INTO object
USING value, object;
return object;
END;
$BODY$
LANGUAGE plpgsql
et quelques variations.
L'appel de set_field_tryX
ne fonctionne pas. Je reçois toujours le message "ERREUR: erreur de syntaxe sur ou près de...".
Comment puis-je y arriver?
Notes:
- Le paramètre est
anyelement
et le champ peut être un champ de type composite. Je ne peux pas utiliser l'objet.nom. - Je suis préoccupé par injection SQL. Tous les conseils dans ce serait appréciée, mais elle n'est pas ma question.
OriginalL'auteur DavidEG | 2011-10-10
Vous devez vous connecter pour publier un commentaire.
Plus vite avec
hstore
Depuis Postgres 9.0, avec la module supplémentaire
hstore
installé dans votre base de données, il est très simple et rapide de la solution avec le#=
opérateur que ...Pour installer le module:
Exemples:
Les valeurs doivent être exprimées pour
text
et le dos, évidemment.Exemple plpgsql fonctions avec plus de détails:
Presque aussi rapide avec
json
Y sont similaires, mais actuellement, les sans-papiers (pg 9.5) des solutions avec
json
(pg 9.3+) oujsonb
(pg 9.4+), construit dans la Postgres, de sorte que vous n'avez pas besoin d'un module supplémentaire.Voir @Geir ajoutée répondre pour plus de détails.
Sans
hstore
etjson
Si vous êtes sur une version plus ancienne ou ne pouvez pas installer le module supplémentaire
hstore
ou ne peut pas supposer qu'il est installé, ici est une version améliorée de ce que j'ai posté précédemment. Encore plus lent que lehstore
opérateur:Appel:
Notes
Un cast explicite de la valeur
_val
pour le type de données cible n'est pas nécessaire, un littéral de chaîne dans la dynamique de la requête serait contraint automatiquement, ce qui évite la sous-requête surpg_type
. Mais j'ai pris un peu plus loin:Remplacer
quote_literal(_val)
avec la valeur directe d'insertion par l'USING
clause. La sauvegarde d'un appel de fonction et deux plâtres, et est plus sûr de toute façon.text
est contrainte pour le type de cible automatiquement moderne PostgreSQL. (N'a pas de test avec les versions avant 9.1.)array_to_string(ARRAY())
est plus rapide questring_agg()
.Pas de variables, pas de
DECLARE
. De moins en moins de devoirs.Pas de sous-requête dans le SQL dynamique.
($1).field
est plus rapide.pg_typeof(_comp_val)::text::regclass
fait la même chose
(SELECT typrelid FROM pg_catalog.pg_type WHERE oid = pg_typeof($1)::oid)
pour la validité des types de composé, juste plus rapide.
Cette dernière modification est construit sur l'hypothèse que
pg_type.typname
est toujours identique à celle de l'associépg_class.relname
pour les inscrits types de composé, et le double en fonte peut remplacer la sous-requête. J'ai couru ce test dans une grosse base de données à vérifier, et il est venu vide comme prévu:L'utilisation d'un
INOUT
paramètre élimine la nécessité expresse de laRETURN
. C'est juste un raccourci de notation. Pavel ne l'aime, il préfère expliciteRETURN
déclaration ...Tout mis ensemble, c'est presque deux fois plus vite que la version précédente.
D'origine (obsolète) réponse:
Le résultat est une version ~ 2,25 fois plus rapide. Mais j'ai probablement ne pourrait pas le faire sans la construction sur Pavel deuxième version.
En outre, cette version évite la plupart du casting de de texte et d'arrière en faisant le tout dans une seule requête, de sorte qu'il devrait être beaucoup moins sujettes à erreur.
Testé avec PostgreSQL 9.0 et 9.1.
merci, cela vaut la peine de dix upvotes, venant de toi. 🙂
Merci @Erwin. Je suis toujours sur cette. J'ai trouvé votre solution échoue avec ce:
CREATE TYPE a as (a1 int); CREATE TYPE b as (b1 a); SELECT setfield3(null::b, 'b1', '(2)');
. Dans ce cas setfield2 de @Pavel fonctionne, mais setfield2 échoue dans certains cas où setfield3 œuvres 😕PostgreSQL 9.1.0
bon micro d'optimisation. Je n'ai rien contre INOUT variables. Juste j'aime explicite de l'utilisation de l'instruction de RETOUR (comme ADA). Explicite le retour de la valeur par l'utilisation de l'instruction RETURN est un peu plus robuste aux erreurs humaines, mais quelque part n'est pas possible (en PL/pgSQL).
OriginalL'auteur Erwin Brandstetter
J'ai écrit une deuxième version de setfield fonction. Il travail sur postgres 9.1 je n'ai pas tester sur les versions plus anciennes. Ce n'est pas un miracle (de performance), mais il est plus robuste et environ 8 fois plus rapide que la précédente.
Je fixe deux bugs. Espère que c'est ok avec vous? 1) Pas de quote_ident() pour le type! Elle allait se briser en pg 9.0 avec un type dans un schéma ("monschema.mytype"). 2) Doubler les guillemets simples, trop! De Plus, remplacé
\'
avec''
pour éviter l'évasion des chaînes de caractères.Je me rends compte maintenant, que le type a besoin d'un
quote_ident()
ou il n'est pas sûr à l'encontre de SQLi. J'ai donc défait que modifier. Cependant, ceci ne fonctionnera pas avec un schéma qualifiés de type. Vous devezquote_ident()
schéma et typename séparément:"myschema"."mytype"
.Je travaille toujours sur ce. J'ai trouvé une erreur, étant donné les types
CREATE TYPE a as (a1 int); CREATE TYPE b as (b1 a); CREATE TYPE c as (c1 b[]);
de courseSELECT setfield2(null::c, 'c1', '{"(\"(2)\")"}');
échoue.Je ne suis pas en mesure d'appeler cette fonction à partir d'un déclencheur. Quand je l'appelle à l'aide d'un tableau, comme l'a suggéré, c'est à dire
NULL::table_name
, je reçois un colonne "tableoid" ne trouve pas dans le type de données "table_name" lors de l'appel de la fonction. Une idée? J'utilise postgresql v. 9.1.OriginalL'auteur Pavel Stehule
Mise à JOUR/attention: Erwin souligne que c'est actuellement sans papiers, et la manuel indique qu'il ne devrait pas être possible de modifier les enregistrements de cette façon.
Utilisation hstore ou Pavel solution à la place.
Cette simple json en fonction de la solution est presque aussi rapide que hstore, et ne nécessite que Postgres 9.3 ou plus récent. Ce devrait être une bonne option si vous ne pouvez pas utiliser la hstore extension, et la différence de performance devrait être négligeable. Référence: https://stackoverflow.com/a/28673542/1914376
un), On peut soit le faire en fonte/concat. Json fonction nécessite Postgresql 9.3:
b) ou en ligne à l'aide de fonctions de Postgres 9.4.
Note: j'ai choisi json_object(TABLEAU[clé,valeur]), car il est un peu plus rapide que json_build_object(clé,valeur):
Pour masquer la coulée de détails, vous pouvez en utiliser un) dans une fonction, avec peu de surcharge.
OriginalL'auteur Geir Bostad
"SELECT" à l'extérieur plpgsql (en SQL dynamique contexte) a différents sens que prévu - il stocker un résultat de requête à la table.
Modification de n'importe quel champ est possible, mais pas simple
Mais ce code n'est pas très efficace, n'est pas possible d'écrire ce bien en plpgsql. Vous pouvez trouver certains de la bibliothèque C, ça devrait le faire.
Merci @Pavel. Même si c'est peut-être pas le meilleur, c'est assez pour moi. Mais maintenant j'ai un autre problème, la colonne n'est pas toujours
text
et quand il tente de mettre à jour j'aicolumn "x" is of type real but expression is of type text
. Comment puis-je dynamiquement en fonte?il travaille dans mon 9.1 - la solution la plus simple est la surcharge setfield fonction de double precission: CRÉER OU de REMPLACER la FONCTION publique.setfield(anyelement, texte, double précision) RENVOIE anyelement LANGAGE plpgsql $EN fonction de$ de commencer à créer de la table temporaire aux select $1.*; exécuter 'mise à jour aux set' || quote_ident($2) || ' = ' || $3; sélectionnez dans $1 * aux; drop table auxiliaire; return $1; end; $fonction$
Dans le cadre de l'écriture d'une autre version, j'ai couru des tests approfondis sur postgresql 9.0. Cette fonction de la capacité maximum de la mémoire partagée sur un test avec un couple de milliers de lignes. Le serveur avait décent ressources. Ce n'est donc pas adaptée à son utilisation. AVERTISSEMENT: de mémoire partagée CONTEXTE: l'instruction SQL "create table temporaire aux select $1.*" Fonctions PL/pgSQL "setfield" de la ligne 2 à l'instruction SQL
OriginalL'auteur Pavel Stehule
Installation d'essai et les critères de référence v2
Erwin encouragés à reproduire son indice de référence dans ce fil (https://stackoverflow.com/a/7782839/1914376), j'ai donc modifié son code avec essai de synthèse des données et ajouté à la fois la hstore solution et le json-solution de ma réponse (et une json solution par Pavel trouvé dans un autre thread) de L'indice de référence est maintenant exécuter en tant qu'une requête, le rendant plus facile de saisir les résultats.
Des résultats des tests de 9.4.1, win32, i5-4300U
hstore
solution est disponible depuis le 9.0 comme indiqué dans ma réponse. 2. hstore fonction devrait être encore plus rapide la prise de(anyelement, hstore)
, l'enregistrement redondant jette. Vous n'avez pas réellement besoin d'une fonction à tous, juste l'expressionmy_record #= hsore(field, value)
. 3. Vous avez compris ma première réponse (ma référence est dépassé), mais pas le bien amélioration de la version ultérieure dans ma réponse.J'ai corrigé le 9.1/9.0 faute de frappe, et vient de déposer le hstore fonction. Whoups! J'ai un peu raté votre amélioration de la réponse dans le banc, il est maintenant inclus:). J'ai également ajouté json comme une ligne de test. Il est intéressant de voir que json_object(TABLEAU[clé,valeur]) est plus rapide que json_build_object(clé,valeur)
Excellent post. Très intéressante. Une chose de plus: fonction de la volatilité. Erwin 1, Erwin 2 et Pavel 2 peut être
STABLE
(mis à jour mon ancienne réponse, trop), Pavel3 et Geir 1 peut êtreIMMUTABLE
. Qui pourrait faire une différence - si ce n'est lors de ce test, dans le contexte des requêtes plus complexes.Ouais, certains complexes des cas d'utilisation doit être en mesure d'exploiter immuable (même entrée plusieurs fois?) Dans ce synthétiques de référence de la fonction en fait deux fois plus lent avec
IMMUTABLE
.plus Lent avec
IMMUTABLE
, c'est étrange. Lequel? Voici une récente question, oùIMMUTABLE
fait toute la différence (pas si évident): stackoverflow.com/questions/28899042/...OriginalL'auteur Geir Bostad
Mise À Jour De Mars 2015:
Largement dépassée aujourd'hui. Envisager la nouvelle référence par @Geir plus rapide, des variantes.
Installation d'essai et les critères de référence
J'ai pris les trois solutions présentées (par Oct. 16th, 2011) et a couru un test sur PostgreSQL 9.0.
Vous trouverez la totalité de l'installation ci-dessous. Seules les données de test ne sont pas inclus comme je l'ai utilisé une vraie vie de base de données (pas de données synthétiques). Tout est encapsulé dans son propre schéma pour les non-intrusif d'utilisation.
Je voudrais encourager tous ceux qui souhaitent reproduire le test. Peut-être avec postgres 9.1? Et d'ajouter vos résultats ici? 🙂
Repères
J'ai couru les requêtes une couple de fois pour remplir le cache. Les résultats présentés sont les meilleurs de un total de cinq exécutions avec
EXPLAIN ANALYZE
.Rirst ronde avec 1000 lignes
Pavel premier prototype de maxes de la mémoire partagée avec d'autres lignes.
Pavel 1: 2445.112 ms
Pavel 2: 263.753 ms
Erwin 1: 120.671 ms
Un autre test avec 5183 lignes.
Pavel 2: 1327.429 ms
Erwin1: 588.691 ms
Merci, j'ai posté une nouvelle solution avec une reproductibilité de référence basé sur votre code. Doit-on "fusion" de l'indice de référence des postes en quelque sorte à la rendre plus facile à suivre pour les nouveaux lecteurs? Toutes les suggestions sont appréciés=)
OriginalL'auteur Erwin Brandstetter