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 arr…
Tag: plpgsql
How do I return a table from a function with a bespoke column name?
This function works: But when I try to add some text to the column name: This just fails as a syntax error on $1. Or: select * from a(‘some prefix’) doesn’t work. Is there some other syntax that does the job? Answer That’s simply not possible. SQL does not allow dynamic column names. Y…
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 optiona…
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 …
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 origina…
How to run a string containing a static SQL query in PostgreSQL?
I have a function which generates a static SQL query and returns it as a string. What I need to do is to run the returned string as if I was typing it directly in psql. Here is a very simplified test case (the true mechanism is far more complicated but the following test case at least shows the idea)
query has no destination for result data, select functions names from schema
I am trying to turn select into function, but keep getting error: “query has no destination for result data”. Select returns 5 columns so I am trying to return table with 5 columns. I can not figure out what I am missing, please help! Answer As the error message suggests, you have to tell the func…
Updating a specified row on a table with information from another table using a trigger and function on PostgreSQL
I have a database for a petshop for class that contains the table Animals with IDs and the dates of the last consult requisition for each animal, amongst other irrelevant columns and the table Requisition which contains the animals’ IDs and requisition dates. And I need to create a trigger that will upd…
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 t…
How to automatically create a comment on CREATE in Postgres?
I would like to create a trigger which gets fired once a table is created in a schema. This trigger should add a comment to the newly created table, something like: With add_comment doing somthibg like: However, it seems I can only add triggers on table level. How could I achieve to add comments to newly crea…