Why select statement inside stored procedure in PostgreSQL throws error 'column does not exist..?

Question

I have this stored procedure in PostgreSQL where I want to make an insert in a table. I get some parameters from the procedure and using them I have tried to select other attributes on other tables.

This is my stored procedure:

CREATE OR REPLACE FUNCTION "public"."prc_sales_invoice_header_insert"("customercode" varchar, "sales_note" varchar, "automatic_payment_id" int4, "cash_register_code" varchar,...etc)

RETURNS "pg_catalog"."void" AS $BODY$

    --declaring variables to store data from the tables
    DECLARE 
    salesdate date;
    salesdocumentserial varchar;
    currencycode varchar;
    currencycode2 varchar;
    customername varchar;
    warehousecode varchar;
    ......etc.

BEGIN
--getting values from tables and storing to variables
SELECT "name" into customername from public."customers" where "customer_code" = customercode;

SELECT CURRENT_DATE into salesdate;
SELECT max(sales_invoice_header_id) into salesdocumentserial from public."sales_invoice_header";
.....

--inserting values
    INSERT INTO public."sales_invoice_header"("sales_date", 
    "sales_document_serial", 
    "currency_code", 
    "currency_code2", 
    "customer_code", 
  ....
VALUES(
    salesdate, 
    salesdocumentserial, 
    currencycode, 
    currencycode2, 
    customer_code,
  .....)
END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100

When I try to execute it throws an error saying: "ERROR: column "customer_code" does not exist", "HINT: There is a column named "customer_code" in table "sales_invoice_header", but it cannot be referenced from this part of the query."

Table customers exists and there is a column named costomer_code but I don't understand why it cannot reference it.

Table customers:

-- ----------------------------
-- Table structure for customers
-- ----------------------------
DROP TABLE IF EXISTS "public"."customers";
CREATE TABLE "public"."customers" (
  "customer_id" int4 NOT NULL DEFAULT nextval('"Customers_CustomerId_seq"'::regclass),
  "customer_code" varchar COLLATE "pg_catalog"."default",
  "barcode" varchar COLLATE "pg_catalog"."default",
  "qr_code" varchar COLLATE "pg_catalog"."default",
  "tax_id" varchar COLLATE "pg_catalog"."default",
  "business_id" varchar COLLATE "pg_catalog"."default",
  "city_id" int8,
  "mobile" varchar COLLATE "pg_catalog"."default",
  "accounting_number" varchar COLLATE "pg_catalog"."default",
  "name" varchar COLLATE "pg_catalog"."default"
);

Can anyone help me with this, what I am doing wrong? Or is this the correct way of doing things?

Thanks in advance.

Please edit your question and add the create table statement for the table customers — user330315
– user330315, Commented Mar 20, 2018 at 12:50
SELECT max(sales_invoice_header_id) into salesdocumentserial is a horrible way to generate IDs. And SELECT CURRENT_DATE into salesdate; can be simplified to salesdate := current_date; — user330315
– user330315, Commented Mar 20, 2018 at 12:51
@a_horse_with_no_name I know about IDs, it's not gonna stay like that, just an example. Updated the customer table. — IldiX
– IldiX, Commented Mar 20, 2018 at 12:58

Vao Tsun · Accepted Answer · 2018-03-20 13:16:51Z

1

    INSERT INTO public."sales_invoice_header"("sales_date", 
    "sales_document_serial", 
    "currency_code", 
    "currency_code2", 
    "customer_code", 
  ....
VALUES(
    salesdate, 
    salesdocumentserial, 
    currencycode, 
    currencycode2, 
    customer_code,

here customer_code is not a variable, nor literal - it is a column name. yet you don't select it form table - you try to use it in VALUES - won't work. either use

insert into ... select ...,customer_code from sales_invoice_header

or

insert into ... values(..., VAR_customer_code)

or

insert into ... values(..., 'value_customer_code')

edit:

as a_horse_with_no_name noticed my VAR_customer_code, must be your customercode first argument?..

edited Mar 20, 2018 at 13:16

answered Mar 20, 2018 at 13:09

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

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

2 Comments

user330315 Over a year ago

Good catch. Should probably be "customercode" as that is the name of the parameter

IldiX Over a year ago

Yes, that was the problem. Needed a fresh eyes :p. Thanks a lot, both!

Collectives™ on Stack Overflow

Why select statement inside stored procedure in PostgreSQL throws error 'column does not exist..?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related