CODEWITHSUNDEEP
sqlpostgresqlstored-procedures
Dan
Dan

Reputation: 2509

how to return string id from insert stored procedure in postgresql

I am trying to return a string id after the insert statement runs in a stored procedure.

Here is my stored procedure code:

CREATE OR REPLACE FUNCTION create_quote(
    p_id character varying(40),
    p_name character varying(200),
    p_website character varying(200),
    p_phone character varying(100),
    p_email character varying(200),
    p_msg text,
    p_created_on timestamp without time zone,
    p_created_ip cidr,
    p_contacted boolean,
    p_contacted_on timestamp without time zone
)
RETURNS character varying(40) AS $body$
BEGIN
    INSERT INTO quote VALUES (p_id, p_name, p_website, p_phone, p_email, p_msg, p_created_on, p_created_ip, p_contacted, p_contacted_on)
    ON CONFLICT (id)
       DO UPDATE SET name = p_name, website = p_website, phone = p_phone, email = p_email, msg = p_msg, created_on = p_created_on, created_ip = p_created_ip, contacted 
= p_contacted, contacted_on = p_contacted_on RETURNING id;
END;
$body$ LANGUAGE plpgsql;

The table schema:

                               Table "public.quote"
    Column    |            Type             |              Modifiers               
--------------+-----------------------------+--------------------------------------
 id           | character varying(40)       | not null
 name         | character varying(200)      | not null
 website      | character varying(200)      | not null
 phone        | character varying(100)      | not null
 email        | character varying(200)      | not null
 msg          | text                        | 
 created_on   | timestamp without time zone | default timezone('utc'::text, now())
 created_ip   | cidr                        | 
 contacted    | boolean                     | 
 contacted_on | timestamp without time zone | 
Indexes:
    "quote_pkey" PRIMARY KEY, btree (id)
    "quote_email_key" UNIQUE CONSTRAINT, btree (email)

Here is the error I get when I try to call the procedure from the psql client.

select * from create_quote("qw2e3r4t5y6u7i8o9i8u7y6t5r4e3w2q1", "name", "website", "phone", "email", "msg", now()::timestamp, NULL, 0, now()::timestamp);
ERROR:  column "qw2e3r4t5y6u7i8o9i8u7y6t5r4e3w2q1" does not exist
LINE 1: select * from create_quote("qw2e3r4t5y6u7i8o9i8u7y6t5r4e3w2q...

I am a bit confused as I am not passing in a column and just a string id for the first parameter. What am I doing wrong?

I made the changes that clemens mentioned below but now i am getting this error:

select create_quote('qw2e3r4t5y6u7i8o9i8u7y6t5r4e3w2q1', 'name', 'website', 'phone', 'email', 'msg', now()::timestamp, NULL, 0, now()::timestamp);
ERROR:  function create_quote(unknown, unknown, unknown, unknown, unknown, unknown, timestamp without time zone, unknown, integer, timestamp without time zone) does not exist
LINE 1: select create_quote('qw2e3r4t5y6u7i8o9i8u7y6t5r4e3w2q1', 'na...
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

Thanks

Upvotes: 0

Views: 2935

Answers (1)

clemens
clemens

Reputation: 17712

You have several errors in your code:

  1. String in SQL are limited with single quotes. Double quotes are there to protect identifiers (e.g. case sensitive column names). Use 'qw2e3r4t5y6u7i8o9i8u7y6t5r4e3w2q1' instead of "qw2e3r4t5y6u7i8o9i8u7y6t5r4e3w2q1" for instance.

  2. Your function is returning a scalar and not a set. You shouldn't call it from the FROM clause.

    SELECT create_quote('qw2e3r4t5y6u7i8o9i8u7y6t5r4e3w2q1', 'name', 'website', 'phone', 'email', 'msg', now()::timestamp, NULL, 0, now()::timestamp);

  3. Your function doesn't return a value. You can assign the result from the UPSERT to a variable and returning this. Or much easier returning the input parameter p_id, because your UPSERT will always return this value.

    CREATE OR REPLACE FUNCTION create_quote(
    p_id character varying(40),
    p_name character varying(200),
    p_website character varying(200),
    p_phone character varying(100),
    p_email character varying(200),
    p_msg text,
    p_created_on timestamp without time zone,
    p_created_ip cidr,
    p_contacted boolean,
    p_contacted_on timestamp without time zone

)
RETURNS character varying(40) AS $body$
BEGIN
    INSERT INTO quote VALUES (p_id, p_name, p_website, p_phone, p_email, p_msg, p_created_on, p_created_ip, p_contacted, p_contacted_on)
    ON CONFLICT (id)
        DO UPDATE SET name = p_name, website = p_website, phone = p_phone, email = p_email, msg = p_msg, created_on = p_created_on, created_ip = p_created_ip, contacted 
= p_contacted, contacted_on = p_contacted_on;
    RETURN p_id;
END;
$body$ LANGUAGE plpgsql;

Upvotes: 1

Related Questions