Feedback-ID: i76984098:Fastmail
Message-ID: <70fdcda5-b4a8-4876-baa4-528cc80e057a@aklaver.com>
Date: Wed, 2 Jul 2025 09:25:14 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: PLPGSQL - store fetched records in array of record
To: Durumdara <durumdara@gmail.com>, Ron Johnson <ronljohnsonjr@gmail.com>
Cc: pgsql-general <pgsql-general@postgresql.org>
References: <CAEcMXhmEJjrqruc0RUSNeUiUzzCf7K_TxuUAzFZoNBi_UzQdzA@mail.gmail.com>
 <CANzqJaAGjTUSMm8fMp-8JOx70WpsjYoF5r-WpF__ZnSftGShJg@mail.gmail.com>
 <CAEcMXhmHuVg7kbcqekULOxckOYZRaTHkO4Es8vJeUQAT2wyJPg@mail.gmail.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <CAEcMXhmHuVg7kbcqekULOxckOYZRaTHkO4Es8vJeUQAT2wyJPg@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/70fdcda5-b4a8-4876-baa4-528cc80e057a%40aklaver.com>
Precedence: bulk

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