All Questions
73 questions
2
votes
1
answer
129
views
Delete data from child tables with Stored Procedure
I have a schema named new_schema with a table result. This table has many child tables with inheritance, named like result_23, result_45, etc.
I have 3500 millions of rows and the database coming slow....
- 73
0
votes
1
answer
557
views
How to return value from stored procedure?
CREATE OR REPLACE PROCEDURE distances(IN flight_num1 Integer)
LANGUAGE 'plpgsql'
AS
DECLARE
BEGIN
SELECT
*,
CASE
WHEN distance_miles >= 0 AND distance_miles <= ...
0
votes
2
answers
990
views
PostgreSQL – How to know specific name or OID of a function/procedure that was just created?
If a CREATE FUNCTION/PROCEDURE is executed, is there any way to get the oid or the specific name of the object that was just created?
It seems the only way is to look tediously look at the parameter ...
- 1,718
3
votes
0
answers
72
views
Problem with stored procedure for queue position management on PostgreSQL
I am creating a database of local library book queue on PostgreSQL. The library members can insert queue into the table book_queue for his or her desired queue position based on the book that he or ...
- 49
0
votes
1
answer
94
views
How to write a select function for converting table data to JSON?
I am using postgresql sp function for getting table data in JSON format.
But i am getting this error.
structure of query does not match function result type DETAIL:
Returned type json does not match ...
0
votes
0
answers
108
views
SQL Error [42601]: ERROR: syntax error at or near "$1" Where: SQL statement in PL/PgSQL function "sp_count_recon_refresh" near line 7 [duplicate]
Aim : To update the column tgt_tbl_count of recon_count1 table with count of each row of tgt_tbl
Error : SQL Error [42601]: ERROR: syntax error at or near "$1".Where: SQL statement in PL/...
- 61
1
vote
5
answers
13k
views
Error calling procedure in posgres "No procedure matches the given name and argument types. You might need to add explicit type casts."
CREATE or replace PROCEDURE mytransactions (n_transactions_id VARCHAR,
n_transaction_amount SMALLINT,
n_transaction_date TIMESTAMP,
n_Delivery_date Date,
n_customer_id VARCHAR,
n_product_id VARCHAR,
...
0
votes
0
answers
46
views
How To Loop Array List In Postegress D
I Want to Loop Through A Array list in Postegres Sql
here is my query
do $$
declare
jsn JSONB;
j JSONB;
arj JSONB[];
begin
jsn = to_jsonb('{"lst" : [{"a&...
- 53
0
votes
0
answers
47
views
Is there a way to return list of 10 rows of data at a time using a where condition from a database using native query?
I'm currently using a database where I'll be sending 10 unique Id's at a time and I'm expecting a list of objects in return.
Is there any way where I can use a native query to get this.
select * from ...
- 11
-1
votes
1
answer
2k
views
update in a function sql in dbeaver/postgres
I have to update a field in a table with concat() and I'm thinking to use a function with an update sql. I also want to have a rollback if update doesnt' work.
I have this function but it just works ...
- 309
0
votes
1
answer
444
views
select ... into variable from table where 1=0 leads to the replacement of the variable with null
We are migrating a lot of code from SQL Server to Postgresql. We met the following problem, a serious difference between SQL Server and Postgresql.
Of course, below, by the expression 1=0, I meant ...
- 84
0
votes
1
answer
771
views
Is it possible to retrieve tables with stored procedures?
It's the first time I use PostgreSQL and its most popular interface, Pgadmin4. I noticed I couldn't get tables using SELECT inside a stored procedure, but managed to do it with a function that returns ...
0
votes
0
answers
956
views
Stored procedure to batch update a table in postgresql
I am running an Extract Transform Load job, triggered every minute, that inserts or updates a few rows (1k) in a table T. Table T has 4.5 million rows.
A second Job needs to update a field with a ...
1
vote
1
answer
314
views
How do you get the values from a stored procedure in postgresql into a java application? I keep getting an error
I have created a stored procedure in my postgres database with a simple SELECT statement:
SELECT DISTINCT device FROM dev_op_test ORDER BY device;
In the java app I am trying to get the list of ...
2
votes
1
answer
46
views
Monitor the activity of a table (number of select on a specific row)
I have a large dataset
Table: id | info1 | info2 | ...
There are multiples process accessing the data heavily.
Is there a built-in way in postgresql or an extension to know the number of time a row is ...
- 23