Skip to content
Advertisement

How do I parameterize table & column in a Postgres-custom-function, selecting PK if value exists, otherwise insert it and return PK anyways?

Trying to do what I specified in the title, I already got the upsert-functionalities working, however when I try to parameterize it, I’m just out of my depth and can’t debug it.

My query:

now when I try to use the function, I get:

What puzzles me about this is the fact that this syntax works fine in an unparameterized version:

https://dbfiddle.uk/?rdbms=postgres_14&fiddle=765389a746d3a392bc646fbedb7ed3b3

My attempts at parameterization:

https://dbfiddle.uk/?rdbms=postgres_14&fiddle=1bffab45d8a9587342a7c3253ea35fc8

https://dbfiddle.uk/?rdbms=postgres_14&fiddle=de6ba235aa21dae33b922f8fddac3b63

Thank you very much in advance, first time posting so if there’s anything I should do differently when asking a question, I’m happy about feedback

edit: this is my function call:

Advertisement

Answer

Do not concatenate strings like that. The function format() makes your life much easier (safer), e.g.

  • %I will wrap the identifiers with double quote, which is handy when tables or columns are case sensitive of contain special characters.
  • %L will wrap the literals with single quotes
  • 1$, 2$ and 3$ are the variables positions provided in the format() call, which is quite handy if one variable is used more than once.

Demo: db<>fiddle

User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement