What is wrong with my stored procedure SQL query on PostgreSQL?

Question

CREATE OR REPLACE FUNCTION AddNewBusinessWithAd(
      business_id IN DECIMAL, accountname IN VARCHAR, business_name IN VARCHAR, primary_contact IN VARCHAR, 
      phone_number IN DECIMAL, business_email IN VARCHAR, business_website IN VARCHAR, 
      ad_id IN DECIMAL, requested_on IN DATE, advertising_type IN VARCHAR, advertising_plan IN TEXT,
      payment_id IN DECIMAL, payment_method IN DECIMAL, billing_address IN VARCHAR)
RETURNS VOID
AS
$proc$
BEGIN
  INSERT INTO Business(business_id, accountname, business_name, primary_contact, phone_number, business_email, business_website)
  VALUES((nextval('business_seq'), accountname, business_name, primary_contact, phone_number, business_email, business_website);
         
  INSERT INTO Advertisement(ad_id, business_id, requested_on, advertising_type, advertising_plan)
  VALUES((nextval('advertisement_seq'), currval('business_seq'), requested_on, advertising_type, advertising_plan);
         
  INSERT INTO Payment(payment_id, ad_id, payment_method, billing_address)
  VALUES((nextval('payment_seq'),currval('advertisement_seq'), payment_method, billing_address);
END;
$proc$ LANGUAGE plpgsql

I tried many times with or without the semicolon but still shows me the error message.

ERROR: syntax error at or near "INSERT" LINE 13: INSERT INTO Advertisement(ad_id, business_id, requested_on... ^ SQL state: 42601 Character: 712

I am sure the table constraint and sequence are all correct but just can not figure out what is wrong with this query.

Gordon Linoff · Accepted Answer · 2021-06-12 16:50:57Z

1

I don't think your parentheses match:

  INSERT INTO Business(business_id, accountname, business_name, primary_contact, phone_number, business_email, business_website)
      VALUES ((nextval('business_seq'), accountname, business_name, primary_contact, phone_number, business_email, business_website);
--------------^

You have two open parens after VALUES.

answered Jun 12, 2021 at 16:50

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

What is wrong with my stored procedure SQL query on PostgreSQL?

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