Thread: PLPGSQL - store fetched records in array of record
Hello!
I have to store some fetched records into two lists (arrays) to work with them.
I have to store some fetched records into two lists (arrays) to work with them.
I can use the RECORD type in a FOR SELECT loop to get one row data.
declare
f record;
begin
for f in select title, length
But if I tried to define an "array of record", I got an error message.
declare
R_A record[]; <---- some error
So I can't store the fetched data into an array simply.
Ok, I found a solution when I define a TYPE, and I use this:
declare
f TMy_Record;
R_A TMy_Record[];
Then I can work with the data.
But this means a dependency, so I can't change the TYPE without pre-dropping the stored procedure (and without recreating after).
Is there any way to avoid this? To use a "simple untyped record" in an array without "dependencies"?
Thank you for the answer!
Best regards
dd
On Wednesday, July 2, 2025, Durumdara <durumdara@gmail.com> wrote:
Is there any way to avoid this? To use a "simple untyped record" in an array without "dependencies"?
Use jsonb
David J.
On Wed, Jul 2, 2025 at 8:21 AM Durumdara <durumdara@gmail.com> wrote:
Hello!
I have to store some fetched records into two lists (arrays) to work with them.
There's almost certainly a way to do what you need done without using arrays. Might require a bit of rethinking, though.
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
Sorry.
I forgot to mention that I have two arrays (records).
One for the modifiable elements, and one for the checkable elements.
If there is a conflict between the actual mod. item and one of the checkable items, the checkable item will move to the end of the modification list.
And the actual mod. item starting time could be changed in this process.
First I imagined a temporary table, but I felt this has too much overhead against an "in memory" array.
UPDATE, INSERT, DELETE and reopen after each modification.
UPDATE, INSERT, DELETE and reopen after each modification.
Ron Johnson <ronljohnsonjr@gmail.com> ezt írta (időpont: 2025. júl. 2., Sze, 15:29):
On Wed, Jul 2, 2025 at 8:21 AM Durumdara <durumdara@gmail.com> wrote:--Hello!
I have to store some fetched records into two lists (arrays) to work with them.There's almost certainly a way to do what you need done without using arrays. Might require a bit of rethinking, though.Death to <Redacted>, and butter sauce.Don't boil me, I'm still alive.<Redacted> lobster!
On 7/2/25 07:26, Durumdara wrote:
> Sorry.
> I forgot to mention that I have two arrays (records).
>
> One for the modifiable elements, and one for the checkable elements.
>
> If there is a conflict between the actual mod. item and one of the
> checkable items, the checkable item will move to the end of the
> modification list.
> And the actual mod. item starting time could be changed in this process.
>
> First I imagined a temporary table, but I felt this has too much
> overhead against an "in memory" array.
> UPDATE, INSERT, DELETE and reopen after each modification.
Assuming it is installed and you have the necessary permissions there is
plpython3u. As it is Python it is looser on types. As example:
CREATE OR REPLACE FUNCTION public.python_test()
RETURNS void
LANGUAGE plpython3u
AS $function$
rec_list = []
for row in plpy.cursor("select category_fk, cell_per from cell_per where
cell_per > 1"):
rec_list.append({"cat": row["category_fk"], "cp": row["cell_per"]})
plpy.notice(rec_list)
$function$
;
select * from python_test();
NOTICE: [{'cat': 'HERB 3.5', 'cp': 18}, {'cat': 'H PREM 3.5', 'cp':
18}, {'cat': 'HERB 2.5', 'cp': 32}, {'cat': 'H PREM 2.5', 'cp': 32},
{'cat': 'GER SC 3.5', 'cp': 18}, {'cat': 'SUCCULENTS', 'cp': 18}]
python_test
-------------
(1 row)
>
>
> Ron Johnson <ronljohnsonjr@gmail.com <mailto:ronljohnsonjr@gmail.com>>
> ezt írta (időpont: 2025. júl. 2., Sze, 15:29):
>
> On Wed, Jul 2, 2025 at 8:21 AM Durumdara <durumdara@gmail.com
> <mailto:durumdara@gmail.com>> wrote:
>
> Hello!
>
> I have to store some fetched records into two lists (arrays) to
> work with them.
>
>
> There's almost certainly a way to do what you need done without
> using arrays. Might require a bit of rethinking, though.
> --
> Death to <Redacted>, and butter sauce.
> Don't boil me, I'm still alive.
> <Redacted> lobster!
>
--
Adrian Klaver
adrian.klaver@aklaver.com