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

Postgres function to return number of rows deleted per schema

I’m trying to adapt a Postgres stored procedure into a function in order to provide some feedback to the caller. The procedure conditionally deletes rows in specific schemas, and I’d want the function to do the same, but also return the amount of rows that were deleted for each schema. The original stored procedure is: My current transform into a

ERROR cursor does not exist after first loop in PL/pgSQL

I need to load a large number of csv files in to a PostgreSQL database. I have a table source_files which contains the file paths and a flag which indicates whether a file has already been loaded for all the csv files I need to load. I have written the following code which loads the first file correctly but then