CODEWITHSUNDEEP
postgresqlstored-procedures
IldiX
IldiX

Reputation: 601

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

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.

Upvotes: 1

Views: 585

Answers (1)

Vao Tsun
Vao Tsun

Reputation: 51639

    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?..

Upvotes: 1

Related Questions