How do I store date Variable in Postgres function?

Question

So I am working to create a function that will delete the 1 month worth records from a table. The table is in postgres. As postgres does not have stored procedures I am trying to declare a function with the logic that will insert the 1 month records into a history table and then delete the records from the live table. I have the following code :

CREATE FUNCTION DeleteAndInsertTransaction(Integer)
    RETURNS Void
AS $Body$

SELECT now() into saveTime;

SELECT * INTO public.hist_table  
FROM (select * from public.live_table
    WHERE update < ((SELECT * FROM saveTime) - ($1::text || ' months')::interval)) as sub;

delete from public.live_table
where update < ((SELECT * FROM saveTime) - ($1::text || ' months')::interval);

DROP TABLE saveTime;     

$Body$
Language 'sql';

So the above code compiles fine but when I try to run it by invoking it :- DeleteAndInsertTransaction(27) it gives me an Error: relation "savetime" does not exist and I have no clue what is going on here. If I take out the SELECT now() into saveTime; out of the function bloc and declare it before invoking the function then it runs fine but I need to store the current date into a variable and use that as a constant for the insert and delete and this is going against a huge table and there could be significant time difference between the insert and deletes. Any pointers as to what is going on here ?

Postgrese does have stored procedures : postgresql.org/docs/current/sql-createprocedure.html but for what you are trying to do, a function is just as good. — user330315
– user330315, Commented Feb 20, 2020 at 22:15
“postgres does not have stored procedures” … as a_horse_with_no_name commented, the CREATE PROCEDURE was added to Postgres 11 and later. Search to learn more, reading posts such as this and this. — Basil Bourque
– Basil Bourque, Commented Feb 20, 2020 at 22:35

score 2 · Accepted Answer · 2020-02-20 22:28:57Z

select .. into .. is the deprecated syntax for create table ... as select ... which creates a new table.

So, SELECT now() into saveTime; actually creates a new table (named savetime), and is equivalent to: create table savetime as select now(); - it's not storing something in a variable.

To store a value in a variable, you need to first declare the variable, then you can assign the value. But you can only do that in PL/pgSQL, not SQL

CREATE FUNCTION DeleteAndInsertTransaction(p_num_months integer)
  returns void
as
$Body$
declare
  l_now timestamp;
begin
  l_now := now();
  ...
end;
$body$
language plpgsql;

To insert into an existing table you need

insert into public.hist_table  
select * 
from public.live_table.

To select the rows from the last x month, there is no need to store the current date and time in a variable to begin with. It's also easier to use make_interval() to generate an interval based on a specified unit.

You can simply use

select *
from live_table
where updated_at <= current_date - make_interval(mons => p_pum_months);

And as you don't need a variable, you can actually do all that with a language sql function.

So the function would look something like this:

CREATE FUNCTION DeleteAndInsertTransaction(p_num_months integer)
    RETURNS Void
AS 
$Body$

  insert into public.hist_table  
  select *
  from live_table
  where updated_at < current_date - make_interval(months => p_pum_months);

  delete from public.live_table
  where updated_at < current_date - make_interval(months => p_pum_months);

$Body$
Language sql;

Note that the language name is an identifier and should not be quoted.

You can actually do the DELETE and INSERT in a single statement:

with deleted as (
  delete from public.live_table
  where updated_at <= current_date - make_interval(months => p_pum_months)
  returning * 
)
insert into hist_table
select *
from deleted;

Collectives™ on Stack Overflow

How do I store date Variable in Postgres function?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related