All Questions
40 questions
1
vote
2
answers
51
views
How to pass a list of locations (longitude, latitude) to a PostgreSQL/PostGIS stored function?
I have prepared a DB fiddle for my questions and also list my SQL code below.
I have created a countries_boundaries table, which I intend to fill with .poly files provided at Geofabrik:
CREATE ...
- 23.1k
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
1
answer
181
views
How to convert this into postgresql stored procedures in nodejs
I want to change the postgresql queries to stored procedures in Nodejs.
app.post("/add", async(req, res) => {
try {
const { name, email, hobby } = req.body;
const newStudent = ...
- 93
0
votes
1
answer
1k
views
Create stored procedure with optional parameters in postgres
I am trying to create either a function or a stored procedure in a Postgres database. I don't really know the syntax in Postgres. I am trying the following, but I know it is not complete.
Create or ...
- 73
0
votes
2
answers
95
views
Is logic blocks in stored procedure/server function run sequentially or run parallel like in CTE?
I had a problem that some of the CTE didn't run in the order I wanted, and I had no way to call one from the other.
WITH insert_cte AS (
INSERT INTO some_table
SELECT *
FROM (...) AS ...
- 386
0
votes
3
answers
742
views
Find a list of elements with parent elements filtering by child elements
I have some tables where I store parent child relationships that go into the same table, allowing me to build multi-level relationships with a single table.
An example for this looks like this:
...
- 409
0
votes
2
answers
2k
views
Understanding PLPGSQL basics
I there, I am new to PL/pgSQL, the project I am working on started using it for some data-intensive handling implementations, I am failing to understand some basic foundations, even reviewing the docs ...
- 611
1
vote
1
answer
475
views
How to use a composite type nested with another composite type in PostgreSQL
Good evening everyone, I was trying to make a procedure work with the types I created to clean up a little the Java code that I made to performed some data persistence, but during the creation of the ...
0
votes
1
answer
418
views
How to Write PostgreSQL Stored Procedure to find and kill idle query?
Need to write PostgreSQL Stored Procedure to find and kill Idle queries. Below are the query to find and kill PID:
To find list of PID:
SELECT pid FROM pg_stat_activity where datname='...
2
votes
1
answer
1k
views
In Postgres, how do I re-validate ("type-check") functions and procedures after tables are altered?
In Postgres, functions are validated on creation: for instance, accessing a column that doesn't exist results in a helpful error, even before the function is run. But altering a table doesn't verify ...
- 526
0
votes
1
answer
69
views
SQL aggregation into at most 3 date ranges
I need to write a query in PostgreSQL to aggregate data from a table based on date ranges of at most 3 ranges. Suppose we have the following table:
CREATE TABLE Purchases (
ID int,
PriceCents ...
- 2,537
0
votes
1
answer
1k
views
Postgres declaration type for multiple rows
I have a postgres function that uses a %ROWTYPE declaration. When I execute this function I get a failure about returning more than one row "code":"P0003","message":"...
- 5,211
2
votes
0
answers
2k
views
Slow query performance in postgres function that returns a set of records via return query execute
I have a function that uses a few parameters and returns a set of records. The function doesn't do anything other than run a query, which needs to be contained in the function so that my application ...
- 1,755
0
votes
2
answers
208
views
Run sql procedure and print logs
I have a sql procedure with me:
create or replace function db.func(value varchar) returns text as $$
select distinct key,
from db.func
$$ language SQL STABLE PARALLEL SAFE;
I want ...
- 243
1
vote
1
answer
792
views
How to Loop json Data and count the values in postgres
CREATE OR REPLACE FUNCTION file_compare()
RETURNS text LANGUAGE 'plpgsql'
COST 100 VOLATILE AS $BODY$
DECLARE
filedata text[];
fpo_data jsonb;
inddata jsonb;
f_cardholderid text;
f_call_receipt text;
...
- 545