Skip to content
Advertisement

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

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.

Advertisement

Answer

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.

2 People found this is helpful
Advertisement