Skip to content

Tag: plpgsql

Cannot rollback while a subtransaction is active – Error 2D000

I have written a stored procedure that basically loops over an array of fields and performs some manipulation in the db for each iteration. What I want to achieve is, either all the iterations of loops should occur or neither one of them should occur. So let’s say there were 5 elements in the fields array and the loop iterates

How to convert integer[] to jsonb in a PL/pgSQL code block

How to convert integer[] to jsonb? Answer Use to_jsonb(): Or: array_to_json() is only still useful to get line feeds in json (not jsonb!). The manual: Converts an SQL array to a JSON array. The behavior is the same as to_json except that line feeds will be added between top-level array elements if the optional boolean parameter is true. See: Store

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

Postgresql – Compare a string with a null value

I’m a bit puzzled because I believe the answer to this question is fairly simple but I’ve searched and tried several options and couldn’t find the right answer. The database is a PostgreSQL 13.1 I am using an API which sends a JSON object to a stored function in the database as follows: The function queries a table with the

Using update inside of PL/pgSQL

To begin with, I am a newbie to SQL and PostgreSQL. It might be a silly beginner’s mistake. ^This isn’t working provides me with an error SQL Error [42601]: ERROR: syntax error at or near “update” Where: PL/pgSQL function temporary_function_for_getting(text,bigint,json) line 13 at RETURN QUERY . The update query works fine by itself not sure what i am doing wrong.