Éviter les variables Postgres ambiguës sans préfixes

Vous utilisez les fonctions Postgres avec votre hébergeur ? Ignorez ce que vous avez entendu : nommez vos variables d’entrée comme vous le souhaitez.

Si tu utilises Supabase, Vercel Postgres, Postgres sans serveur au néon ou l’un des de nombreux hébergeurs, vous devrez écrire des fonctions Postgres personnalisées, en particulier lorsque vous traitez des rôles et des transactions. Cependant, vous pouvez nommer vos variables d’entrée comme vous le souhaitez, malgré ce que vous avez lu.

Erreurs courantes

parameter name "id" used more than once

Ne reverrez plus jamais cette erreur.

code: '42702',
details: 'It could refer to either a PL/pgSQL variable or a table column.',
hint: null,
message: 'column reference "id" is ambiguous'

Ou une déclaration d’erreur JSON complexe comme celle-ci.

Préfixes

Disons que nous voulons sélectionner une publication par identifiant :

DROP FUNCTION IF EXISTS get_post_by_id;
CREATE OR REPLACE FUNCTION get_post_by_id(p_id uuid)
RETURNS SETOF posts AS $$
BEGIN
  RETURN QUERY SELECT * FROM posts WHERE id = p_id;
END;
$$ LANGUAGE plpgsql;

Normalement, vous voyez quelque chose comme ceci où le champ id a un préfixe comme p_, même si cela pourrait être n’importe quoi. Je déteste les préfixes et il existe des moyens de les éviter.

Exemple de Supabase

Si j’utilise Supabase, je ne veux pas transmettre mon identifiant comme ceci :

// normally you will already have an id variable from somewhere else in your code
const id = 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11';

const { data, error } = await supabase.rpc('get_post_by_id', { p_id: id });

Quand je devrais pouvoir faire ceci :

const { data, error } = await supabase.rpc('get_post_by_id', { id });

Je sais, je sais, je coupe les cheveux en quatre. Cependant, cela peut vous faire gagner beaucoup de temps en utilisant quelques bonnes pratiques.

1. Utilisez des alias

Dans la fonction, vous devez toujours utiliser cet alias. Cela crée beaucoup de clarté lorsque vos fonctions deviennent complexes. Chaque commande a des règles différentes pour les alias.

Sélectionner

SELECT p.* FROM posts p WHERE p.id = '0x123';

Le posts AS p est également possible, mais pourquoi ajouter un autre mot alors que ce n’est pas nécessaire. Vous n’avez pas non plus besoin du p supplémentaire p.*-c’est juste pour clarifier que vous pouvez ajouter le préfixe dans l’instruction select.

Insérer

RETURN QUERY
INSERT INTO posts AS p (title, content)
VALUES ('title1', 'content1')
RETURNING p.*;

Remarquez que vous avez besoin du AS dans les instructions d’insertion. Encore une fois, le supplément p. est inutile à la fin.

Si tu utilises ON CONFLICTvous devez utiliser le nom de la contrainte, contrairement à la valeur dans SETle conflit VOLONTÉ conflit avec votre variable.

NE FAITES PAS CELA !

RETURN QUERY
INSERT INTO posts AS p (title, content)
VALUES ('title1', 'content1')
ON CONFLICT (id) DO UPDATE

Utilisez plutôt le nom de la contrainte :

RETURN QUERY
INSERT INTO posts AS p (title, content)
VALUES ('title1', 'content1')
ON CONFLICT ON CONSTRAINT posts_pkey DO UPDATE

Mise à jour

UPDATE posts p SET title = 'something' WHERE p.id = '0x123';

Ici, notez que vous ne pouvez pas utiliser d’alias dans l’instruction set. Ne vous inquiétez pas, cela n’entrera en conflit avec rien. Si vous renvoyez les données, n’oubliez pas d’utiliser également un alias :

RETURN QUERY
UPDATE posts p
SET title = 'new title'
WHERE p.id = '0x123'
RETURNING p.id, p.title;

Supprimer

RETURN QUERY
DELETE FROM posts p WHERE p.id = '0x123'
RETURNING p.id;

Et la suppression fonctionne comme prévu puisqu’il n’y a aucune entrée.

2. Utilisez les noms de fonctions

Ce que beaucoup de gens ne réalisent pas, et c’est peut-être la meilleure astuce de cet article, c’est que vous pouvez utiliser le nom de la fonction avec le nom de la variable comme alias. Après tout, c’est le nom technique complet.

SELECT * FROM posts p WHERE p.id = get_post_by_id.id;

Cela signifie que la fonction de travail finale ressemble à ceci :

DROP FUNCTION IF EXISTS get_post_by_id;
CREATE OR REPLACE FUNCTION get_post_by_id(id uuid)
RETURNS SETOF posts AS $$
BEGIN
  RETURN QUERY SELECT * FROM posts p WHERE p.id = get_post_by_id.id;
END;
$$ LANGUAGE plpgsql;

Et vous pourriez l’appeler comme prévu.

Exemple de mise à jour

Voici un exemple de mise à jour pour montrer plus de complexité :

DROP FUNCTION IF EXISTS update_post_title;
CREATE OR REPLACE FUNCTION update_post_title(id uuid, title text)
RETURNS SETOF posts AS $$
BEGIN
  RETURN QUERY
  UPDATE posts p
  SET title = update_post_title.title
  WHERE p.id = update_post_title.id
  RETURNING *;
END;
$$ LANGUAGE plpgsql
SECURITY DEFINER;

Conflits de retour et de paramètres

Mais nous avons un dernier problème à résoudre. Et si l’entrée et la sortie portent le même nom ? AKA, les paramètres et les valeurs de retour ont des variables qui utilisent le même nom ?

Si nous rendons un SETOF posts, pas de problème, mais lorsque nous devons renvoyer une table personnalisée, cela devient confus. En effet, le type de retour n’est en réalité qu’un paramètre de fonction.

NE FAITES PAS CELA !

CREATE FUNCTION test(id uuid)
RETURNS TABLE(id uuid) LANGUAGE plpgsql AS $$
BEGIN
  RETURN QUERY SELECT posts.id FROM posts WHERE posts.id = test.id;
END;
$$;

C’est en fait la même chose que ceci en utilisant IN et OUT déclarations:

NE FAITES PAS CELA !

CREATE FUNCTION test(IN id uuid, OUT id uuid)
LANGUAGE plpgsql AS $$
BEGIN
  RETURN QUERY SELECT posts.id FROM posts WHERE posts.id = test.id;
END;
$$;

Ainsi, comme vous pouvez le constater, nous ne pouvons pas avoir deux paramètres portant le même nom. Ignorez l’inutilité de cette fonction. 😄

1. Renvoie un type JSON

Vous pouvez simplement renvoyer un type JSON (utilisez toujours jsonb sauf si vous savez le contraire).

DROP FUNCTION IF EXISTS update_post_title;
CREATE OR REPLACE FUNCTION update_post_title(id uuid, title text)
RETURNS SETOF jsonb AS $$
BEGIN
  RETURN QUERY
  UPDATE posts p
  SET title = update_post_title.title
  WHERE p.id = update_post_title.id
  RETURNING jsonb_build_object('id', p.id, 'title', p.title);
END;
$$ LANGUAGE plpgsql
SECURITY DEFINER;

2. Utilisez des types personnalisés

Ou vous pouvez utiliser votre propre type personnalisé. Ceci est utile lorsque plusieurs fonctions renvoient le même type de données.

CREATE TYPE post_simple AS (
  id uuid,
  title text
);

DROP FUNCTION IF EXISTS update_post_title;
CREATE OR REPLACE FUNCTION update_post_title(id uuid, title text)
RETURNS SETOF post_simple AS $$
BEGIN
  RETURN QUERY
  UPDATE posts p
  SET title = update_post_title.title
  WHERE p.id = update_post_title.id
  RETURNING p.id, p.title;
END;
$$ LANGUAGE plpgsql
SECURITY DEFINER;

Tricherie…

Et enfin, si tout le reste échoue, Postgres dispose d’une solution de repli pour Conflits de variables. Mettez simplement l’éliminateur de conflits nécessaire avant la déclaration de votre fonction.

J’ai énuméré ce dernier, mais je suis fermement convaincu que vous ne devriez pas en avoir besoin. Structurez correctement vos fonctions !

Celui qui vous dit d’utiliser des préfixes dans les paramètres de fonction a tort. Créez simplement vos fonctions de la bonne manière.