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 …
Tag: plpgsql
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 RETU…
PostgreSQL duplicate key value violates unique constraint while using on conflict do nothing on insert
Table def: I have a function that inserts new rows into the database: It fails with the index Although running the relevant insert on its own with duplicate values it has no problem with it. I can’t understand what seems to be the the difference between the function and the single statement? This also w…
Using like or ilike to create SQL/ PLpgSQL function
I am trying to create function Animals (for convenience purpose) that takes first letter of an animal name and returns the whole name of the animal from the list e.g. C=> Cow, Cat… The problem I am facing is that the string after the like function is being treated as a string (I want it to be input).…
find the highest occurrence of a point type
How to get the highest repetition for a point type data in PgSQL Need to calculate the most repeated coordinate (more precisely within a range of 100 meters) Sample data column name coordinates …
Truncate if exists in psql function and call function
I have the following code to create a function that truncates all rows from the table web_channel2 if the table is not empty: create or replace function truncate_if_exists(tablename text) returns void …
How to edit and return a Cursor in plpgsql?
I am doing a simple query inside a function, and I need to change the values of this query, but only for information. I don’t need to update the table as such. I managed to edit the values but I don’t know how to return them. And below this function will be executed As you can see, the query d…
Order of cascaded deletes in postgres
I encountered a problem which makes me suspect Postgres deletes rows from dependent tables (ON DELETE CASCADE) only after it already deleted the original row. I have these tables: I created a trigger (before delete) on the function_inclusion table: Let’s say I have 2 functions and a function_inclusion: …
Delete rows depending on values from another table
How to delete rows from my customer table depending on values from another table, let’s say orders? If the customer has no active orders they should be able to be deleted from the DB along with their rows (done using CASCADE). However, if they have any active orders at all, they can’t be deleted. …
Expanding a JSON array embedded in an object in Postgres 11.7
Postgres 11.7. An upgrade to PG 12 (JSONPath, I know) is in planning stages, not sure when we’ll get there. I’m working on passing some data to a PL/PgSQL stored function, and am struggling to unpack an array embedded in an object. I’m dealing with some client libraries that Really Like Obje…