I am trying to copy indexes from materialized views onto tables. This is the script I am trying to use: DO $$ declare indexdefname record; a text; b text; c text; begin for indexdefname in …
Tag: plpgsql
Sum variables when condition is met in sql
I have a table mes_transaction where I have variable dc – it’s either DEBIT or CREDIT. I am writing a sql procedure for another table(if I can say so). In the other table mes_aggregated_trx I have trx_amt – transaction amount variable. Basically what I need to do is to write a procedure that would sum trx_amt in mes_aggregated_trx based on
Race Condition between SELECT and INSERT for multiple columns
Note: This is a question which is a follow up of this solution. You need to read the link to get context for this question. Also, this is for postgres v9.4 If we want to return multiple columns now …
Postgresql update specific fields based on a value (trigger)
I am writing a Trigger in Postgresql to update dynamically the content of specific columns of a revenues table based on inserts on a consultations table. [edit] Here is how my tables look like …
Return default rows from a function when first SELECT does not return rows
I have this function http://rextester.com/VIHMIG61446 and I’m looking a way to not repeat the whole query on the if, so I decided to use with as to store the result but it does not work for me http://rextester.com/MVMVA73088 How should I use with as? Answer The if-else logic can be avoided completely. The equivalent result can be written as a
How Do I use or properly return a value from my PostgreSQL function?
========================================= How do I use res in the parent function? Answer don’t specify both OUT and function returns: if you want to use the return of function ,use select into VAR, perform will just execute function discarding its output: finaly: https://www.postgresql.org/docs/current/static/plpgsql.html
Postgres Function to insert multiple records in two tables
create table public.orders ( orderID serial PRIMARY KEY, orderdate timestamp NOT NULL ); create table public.orderdetails ( orderdetailID serial PRIMARY KEY, orderID integer …
Set empty strings (”) to NULL in the whole database
In my database are many text columns where values are empty strings (”). The empty strings need to be set to NULL. I do not know the exact schemas, tables and columns in this database or rather I want to write a general solution which can be reused. How would I write a query / function to find all text
Selecting and passing a record as a function argument
It may look like a duplicate of existing questions (e.g. This one) but they only deal with passing “new” arguments, not selecting rows from the database. I have a table, for example: And a function: I would like to run it on data already existing in the database. It’s no problem if I would like to use it from another
How to iterate over results of query
I am creating a function in pgsql script language, and what I want to do in this point is iterate over the results of a query and for each row do something specific. My current try is the following, where temprow is declared as temprow user_data.users%rowtype. The code in question is the following: However I get the following error from