Postgres PL/Python Initializing record functions

Question

Is there a way to pass into a PL function that is called to give a default value and how do I work with it, the rest of the inserted values?

CREATE TABLE tokensStore (
    id SERIAL PRIMARY KEY,
    users INTEGER REFERENCES users(id),
    genDate timestamp with time zone NOT NULL DEFAULT now(),
    expDate timestamp with time zone NOT NULL DEFAULT now() + interval '24 hours', 
    token char(30) NOT NULL DEFAULT tokenGenerator(%%%INSERTED VALUES%%%)   
);

and also I'm not sure this is a proper way to do this, I don't know if I should allow the NULL value and make a trigger that generates the value for the token.

The thing is that I don't want to allow null values in that column. And I also want to make Inserts with returning value token something like

INSERT tokensStore(user) VALUES (1) RETURNING token;

If anyone has good references on this It would be Highly appreciated.

It seems you need a trigger. Why do you think it should be in plpython (not in plpgsql)? Does it matter? — klin, Jun 14 '18 at 19:21
I'm generating tokens applying a crypto function written in Python, that's why I am using Python — ekiim, Jun 14 '18 at 20:33

score 0 · Accepted Answer · answered Jun 14 '18 at 21:06

You need a plpython trigger:

create or replace function before_insert_on_tokensstore()
returns trigger language plpython3u as $$

    id = TD["new"]["id"]
    users = TD["new"]["users"]
    TD["new"]["token"] = users * 100 + id

    # 
    # TD["new"]["token"] = generate_token(users, id)
    #
    return "MODIFY"

$$;

create trigger before_insert_on_tokensstore
before insert on tokensstore
for each row execute procedure before_insert_on_tokensstore();

Exemplary usage:

insert into tokensstore(users) 
values (1) 
returning token; 

             token              
--------------------------------
 101                           
(1 row)

INSERT 0 1

Of course, with the trigger you do not need a default value for token.

Read about PL/Python Trigger Functions.

Postgres PL/Python Initializing record functions

1 Answers1