CODEWITHSUNDEEP
postgresqlsql-insert
gdubs
gdubs

Reputation: 2754

how do i use the result of a function for an insert statement?

I have a function that returns a varchar

    CREATE OR REPLACE Function HashPassword(in p_email varchar, in p_password varchar)
RETURNS TABLE(
    o_password varchar,
    o_user_id int
) as
$$
    return query select 'myresult', 9999;
END
$$
Language plpgsql;

I want to do an insert into a table that is the result of the function call

insert into table_that_needs_it(id, password, date)
select 99999, (select o_password from HashPassword('myemail', 'mypassword')), CURRENT_TIMESTAMP;

It's giving me the following error

ERROR:  syntax error at or near ";"

Upvotes: 0

Views: 20

Answers (1)

user330315
user330315

Reputation:

Use only a single SELECT:

insert into table_that_needs_it(id, password, date)
select 99999, o_password, CURRENT_TIMESTAMP
from hashpassword('myemail', 'mypassword');

If you also want to use the ID:

insert into table_that_needs_it(id, password, date)
select o_user_id, o_password, CURRENT_TIMESTAMP
from hashpassword('myemail', 'mypassword');

Upvotes: 1

Related Questions