Questions tagged as 'plpgsql'

2
answers

Pass parameter of type ROWTYPE with the EXECUTE command

I am developing a function in Postgres that aims to retrieve for each record of a query the result value of a scan contained in a set of functions. Of these functions only one will return the correct value. These functions have a common prefix '...
asked by 21.01.2016 / 19:14
2
answers

Return records totaling desired value

I have a table in this format, with more N values and N vendors, approximately 100,000 records, and I need to do through a query or a plpgsql function a way to get all the launch numbers num where the sum of valor_conta is 0 (zero)...
asked by 26.06.2017 / 17:52
1
answer

Update from the comparison between two postgres tables

I have a sales table that contains the salesperson's code. But the seller's table was completely redone, with the change of id . How can I update the vendor code in the sales table from the comparison between the new vendor table and th...
asked by 19.09.2016 / 20:07
1
answer

Script to change table names in the PostgreSQL database

I have this script: SELECT n.nspname AS schema_name, c.relname AS table_name, c.reltuples::int AS num_reg FROM pg_class c LEFT JOIN pg_namespace n ON n.oid = c.relnamespace LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace WH...
asked by 02.10.2018 / 04:47
1
answer

How to update column for each SELECT in a given table?

I tried to create a trigger for a table using it so that it would be activated after every select , but by searching a little I discovered that it is not possible to create triggers for selections, only for update, insert ) a...
asked by 13.06.2018 / 18:49
2
answers

Foreign key FK, is each ID number in a table?

Example: - CLIENTE(Num_cliente, Nome_cliente, Cidade) - PEDIDO(Num_pedido, Data_pedido, Num_cliente, Preco_total) - ITEM_PEDIDO(Num_pedido, Num_item, Quantidade) - ITEM(Num_item, Preco_unitario) - EXPEDICAO(Num_pedido, Num_deposito, Data...
asked by 03.09.2018 / 02:56
1
answer

Update an attribute according to two other tables

Company Table CREATE TABLE empresa ( cnpj char(18) PRIMARY KEY, nome varchar(60) NOT NULL UNIQUE, receita float NOT NULL, pais_sede varchar(26) NOT NULL ); Target table CREATE TABLE destino( cidade...
asked by 30.11.2017 / 01:33
1
answer

If exists update else insert in table with foreign key postgres

I need to make an insert in a table with a foreign key, so if that record does not exist in the other table I need to do an insert, can I do this using if exists? Any solution?     
asked by 08.05.2017 / 21:57
1
answer

How can I make a procedure to update the balance in postgres?

Mine has been giving a mistake that I can not understand talking about having a bug in $, but when I used another example procedure from the internet almost in the same way as mine did, then the error is being in how to do it procedure. The o...
asked by 22.11.2018 / 23:13
0
answers

Error due to cancel sale in POSTGRESQL

Good afternoon, I'm implementing a sell rebate function that is as follows: CREATE OR REPLACE FUNCTION estorna_venda(p_id_venda INTEGER) RETURNS VARCHAR AS $BODY$ DECLARE v_id_prod INTEGER; DECLARE v_prod_qtd INTEGER; DECLARE v_contador INTEG...
asked by 18.05.2018 / 21:20