Ошибка синтаксиса примерное положение loop

CREATE OR REPLACE FUNCTION ccdb_dummy.o_bills_new(a integer)
  RETURNS void AS
$BODY$
DECLARE
bill_no bigint;
lc_cin_num bigint;
lc_consumer_num bigint;
lc_bill_type_group_code character varying(10);
t  ccdb_stg.o_bills_stg%ROWTYPE;
t1 ccdb_stg.o_bills_details_stg%ROWTYPE;
BEGIN

    FOR t IN
    SELECT * 
    FROM ccdb_stg.o_bills_stg
    WHERE section_code = a
LOOP
    bill_no := nextval('ccdb_stg_test.bills_seq');

    IF t.consumer_num IS NULL THEN
        lc_consumer_num = t.applicant_num;
    ELSE
        lc_consumer_num = t.consumer_num;

    SELECT bill_type 
    INTO lc_bill_type_group_code
    FROM ccdb_dummy.bill_type_master a
    WHERE a.bill_type_id = t.bill_type_id;

    SELECT cin
    INTO lc_cin_num
    FROM ccdb_dummy.consumers b
    WHERE b.consumer_num = lc_consumer_num;

    INSERT INTO  ccdb_dummy.bills(bill_id,source_system_id,mbc_bill_id,mbc_bill_no,cin,cust_connection_id,consumer_number,cust_type_flag,bill_type_group_code,bill_type_code,bill_month,total_consumption,bill_date,due_date,dc_date,dues_remind_date,dc_remind_date,org_unit_id,parent_bill_id,category_flag,status_flag,conn_cat_subgroup_id,dispute_flag,inst_flag,approved_date,bill_amt,paid_amt,past_arrear,arrear_collected,advance_amount,creation_dt,created_by)
VALUES (bill_no,1,t.mbc_bill_id,t.mbc_bill_no,lc_cin_num,t.cust_connection_id,lc_consumer_num,t.cust_type_flag,t.bill_type_group_code,lc_bill_type_group_code,t.bill_month,
    t.total_consumption,t.bill_date,t.due_date,t.dc_date,t.dues_remaind_date,t.dc_remaind_date,t.section_code,t.parent_bill_id,t.category_flag,t.status_flag,
    t.conn_cat_subgroup_id,t.dispute_flag,t.inst_flag,t.approved_date,t.bill_amt,t.paid_amt,t.past_arrear,t.arrear_collected,t.advance_amount,now(),'system');

    FOR t1 IN
        SELECT *
        FROM ccdb_stg.o_bills_details_stg a 
        WHERE a.mbc_bill_id = t.mbc_bill_id
    LOOP
        INSERT INTO ccdb_dummy.bill_head_details(bill_id,charge_head_code,amount_billed,amount_paid,charge_head_descr,ccdb_update_time,creation_dt,created_by,tariff_id,
                             demand_date)
        VALUES
            (bill_no,t1.charge_head_code,t1.amount_billed,t1.amount_paid,null,now(),now(),'system',t1.tariff_id,t1.demand_date);
    END LOOP;
END LOOP;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION ccdb_dummy.o_bills_new(integer)
  OWNER TO ccdb_admin;

When I try to compile this function, I get the following error:

ERROR:  syntax error at or near "LOOP"
LINE 58:  END LOOP;
              ^
********** Error **********
ERROR: syntax error at or near "LOOP"
SQL state: 42601
Character: 2282

Where is my error? I am sure it must be something silly, but I am not able to figure it out.

PostgreSQL error 42601- How we fix it

by Sijin George | Sep 12, 2019

Syntax errors are quite common while coding.

But, things go for a toss when it results in website errors.

PostgreSQL error 42601 also occurs due to syntax errors in the database queries.

At Bobcares, we often get requests from PostgreSQL users to fix errors as part of our Server Management Services.

Today, let’s check PostgreSQL error in detail and see how our Support Engineers fix it for the customers.

What causes error 42601 in PostgreSQL?

PostgreSQL is an advanced database engine. It is popular for its extensive features and ability to handle complex database situations.

Applications like Instagram, Facebook, Apple, etc rely on the PostgreSQL database.

But what causes error 42601?

PostgreSQL error codes consist of five characters. The first two characters denote the class of errors. And the remaining three characters indicate a specific condition within that class.

Here, 42 in 42601 represent the class “Syntax Error or Access Rule Violation“.

In short, this error mainly occurs due to the syntax errors in the queries executed. A typical error shows up as:

Here, the syntax error has occurred in position 119 near the value “parents” in the query.

How we fix the error?

Now let’s see how our PostgreSQL engineers resolve this error efficiently.

Recently, one of our customers contacted us with this error. He tried to execute the following code,

But, this ended up in PostgreSQL error 42601. And he got the following error message,

Our PostgreSQL Engineers checked the issue and found out the syntax error. The statement in Line 5 was a mix of plain and dynamic SQL. In general, the PostgreSQL query should be either fully dynamic or plain. Therefore, we changed the code as,

This resolved the error 42601, and the code worked fine.

[Need more assistance to solve PostgreSQL error 42601?- We’ll help you.]

Conclusion

In short, PostgreSQL error 42601 occurs due to the syntax errors in the code. Today, in this write-up, we have discussed how our Support Engineers fixed this error for our customers.

PREVENT YOUR SERVER FROM CRASHING!

Never again lose customers to poor server speed! Let us help you.

Our server experts will monitor & maintain your server 24/7 so that it remains lightning fast and secure.

SELECT * FROM long_term_prediction_anomaly WHERE + “‘Timestamp’” + ‘”BETWEEN ‘” +
2019-12-05 09:10:00+ ‘”AND’” + 2019-12-06 09:10:00 + “‘;”)

Hello Joe,
Do you still get PostgreSQL errors? If you need help, we’ll be happy to talk to you on chat (click on the icon at right-bottom).

У меня ошибка drop table exists “companiya”;

CREATE TABLE “companiya” (
“compania_id” int4 NOT NULL,
“fio vladelca” text NOT NULL,
“name” text NOT NULL,
“id_operator” int4 NOT NULL,
“id_uslugi” int4 NOT NULL,
“id_reklama” int4 NOT NULL,
“id_tex-specialist” int4 NOT NULL,
“id_filial” int4 NOT NULL,
CONSTRAINT “_copy_8” PRIMARY KEY (“compania_id”)
);

CREATE TABLE “filial” (
“id_filial” int4 NOT NULL,
“street” text NOT NULL,
“house” int4 NOT NULL,
“city” text NOT NULL,
CONSTRAINT “_copy_5” PRIMARY KEY (“id_filial”)
);

CREATE TABLE “login” (
“id_name” int4 NOT NULL,
“name” char(20) NOT NULL,
“pass” char(20) NOT NULL,
PRIMARY KEY (“id_name”)
);

CREATE TABLE “operator” (
“id_operator” int4 NOT NULL,
“obrabotka obrasheniya” int4 NOT NULL,
“konsultirovanie” text NOT NULL,
“grafick work” date NOT NULL,
CONSTRAINT “_copy_2” PRIMARY KEY (“id_operator”)
);

CREATE TABLE “polsovateli” (
“id_user” int4 NOT NULL,
“id_companiya” int4 NOT NULL,
“id_obrasheniya” int4 NOT NULL,
“id_oshibka” int4 NOT NULL,
CONSTRAINT “_copy_6” PRIMARY KEY (“id_user”)
);

CREATE TABLE “reklama” (
“id_reklama” int4 NOT NULL,
“tele-marketing” text NOT NULL,
“soc-seti” text NOT NULL,
“mobile” int4 NOT NULL,
CONSTRAINT “_copy_3” PRIMARY KEY (“id_reklama”)
);

CREATE TABLE “tex-specialist” (
“id_tex-specialist” int4 NOT NULL,
“grafik” date NOT NULL,
“zarplata” int4 NOT NULL,
“ispravlenie oshibok” int4 NOT NULL,
CONSTRAINT “_copy_7” PRIMARY KEY (“id_tex-specialist”)
);

CREATE TABLE “uslugi” (
“id_uslugi” int4 NOT NULL,
“vostanavlenia parola” int4 NOT NULL,
“poterya acaunta” int4 NOT NULL,
CONSTRAINT “_copy_4” PRIMARY KEY (“id_uslugi”)
);

ALTER TABLE “companiya” ADD CONSTRAINT “fk_companiya_operator_1” FOREIGN KEY (“id_operator”) REFERENCES “operator” (“id_operator”);
ALTER TABLE “companiya” ADD CONSTRAINT “fk_companiya_uslugi_1” FOREIGN KEY (“id_uslugi”) REFERENCES “uslugi” (“id_uslugi”);
ALTER TABLE “companiya” ADD CONSTRAINT “fk_companiya_filial_1” FOREIGN KEY (“id_filial”) REFERENCES “filial” (“id_filial”);
ALTER TABLE “companiya” ADD CONSTRAINT “fk_companiya_reklama_1” FOREIGN KEY (“id_reklama”) REFERENCES “reklama” (“id_reklama”);
ALTER TABLE “companiya” ADD CONSTRAINT “fk_companiya_tex-specialist_1” FOREIGN KEY (“id_tex-specialist”) REFERENCES “tex-specialist” (“id_tex-specialist”);
ALTER TABLE “polsovateli” ADD CONSTRAINT “fk_polsovateli_companiya_1” FOREIGN KEY (“id_companiya”) REFERENCES “companiya” (“compania_id”);

ERROR: ОШИБКА: ошибка синтаксиса (примерное положение: “”companiya””)
LINE 1: drop table exists “companiya”;
^

Источник

@YohDeadfall — I understand that part about it, but this is not script that I am creating or even code that I am creating. This is all created under the hood by Npsql/EntityFramework. My quick guess is that I am extending my DbContext from IdentityDbContext<IdentityUser> which wants to create all of the tables for roles, users, claims, etc. If I change this to just extend from DbContext, then everything works as advertised.

Below is the script that EF is trying to use created from dotnet ef migrations script — please be aware that I have removed my custom part of the script for brevity.

You can see there are two specific calls that are being made where [NormalizedName] and [NormalizedUserName] are being used.

CREATE TABLE IF NOT EXISTS "__EFMigrationsHistory" ( "MigrationId" varchar(150) NOT NULL, "ProductVersion" varchar(32) NOT NULL, CONSTRAINT "PK___EFMigrationsHistory" PRIMARY KEY ("MigrationId") ); CREATE TABLE "AspNetRoles" ( "Id" text NOT NULL, "ConcurrencyStamp" text NULL, "Name" varchar(256) NULL, "NormalizedName" varchar(256) NULL, CONSTRAINT "PK_AspNetRoles" PRIMARY KEY ("Id") ); CREATE TABLE "AspNetUsers" ( "Id" text NOT NULL, "AccessFailedCount" int4 NOT NULL, "ConcurrencyStamp" text NULL, "Email" varchar(256) NULL, "EmailConfirmed" bool NOT NULL, "LockoutEnabled" bool NOT NULL, "LockoutEnd" timestamptz NULL, "NormalizedEmail" varchar(256) NULL, "NormalizedUserName" varchar(256) NULL, "PasswordHash" text NULL, "PhoneNumber" text NULL, "PhoneNumberConfirmed" bool NOT NULL, "SecurityStamp" text NULL, "TwoFactorEnabled" bool NOT NULL, "UserName" varchar(256) NULL, CONSTRAINT "PK_AspNetUsers" PRIMARY KEY ("Id") ); CREATE TABLE "AspNetRoleClaims" ( "Id" int4 NOT NULL, "ClaimType" text NULL, "ClaimValue" text NULL, "RoleId" text NOT NULL, CONSTRAINT "PK_AspNetRoleClaims" PRIMARY KEY ("Id"), CONSTRAINT "FK_AspNetRoleClaims_AspNetRoles_RoleId" FOREIGN KEY ("RoleId") REFERENCES "AspNetRoles" ("Id") ON DELETE CASCADE ); CREATE TABLE "AspNetUserClaims" ( "Id" int4 NOT NULL, "ClaimType" text NULL, "ClaimValue" text NULL, "UserId" text NOT NULL, CONSTRAINT "PK_AspNetUserClaims" PRIMARY KEY ("Id"), CONSTRAINT "FK_AspNetUserClaims_AspNetUsers_UserId" FOREIGN KEY ("UserId") REFERENCES "AspNetUsers" ("Id") ON DELETE CASCADE ); CREATE TABLE "AspNetUserLogins" ( "LoginProvider" text NOT NULL, "ProviderKey" text NOT NULL, "ProviderDisplayName" text NULL, "UserId" text NOT NULL, CONSTRAINT "PK_AspNetUserLogins" PRIMARY KEY ("LoginProvider", "ProviderKey"), CONSTRAINT "FK_AspNetUserLogins_AspNetUsers_UserId" FOREIGN KEY ("UserId") REFERENCES "AspNetUsers" ("Id") ON DELETE CASCADE ); CREATE TABLE "AspNetUserRoles" ( "UserId" text NOT NULL, "RoleId" text NOT NULL, CONSTRAINT "PK_AspNetUserRoles" PRIMARY KEY ("UserId", "RoleId"), CONSTRAINT "FK_AspNetUserRoles_AspNetRoles_RoleId" FOREIGN KEY ("RoleId") REFERENCES "AspNetRoles" ("Id") ON DELETE CASCADE, CONSTRAINT "FK_AspNetUserRoles_AspNetUsers_UserId" FOREIGN KEY ("UserId") REFERENCES "AspNetUsers" ("Id") ON DELETE CASCADE ); CREATE TABLE "AspNetUserTokens" ( "UserId" text NOT NULL, "LoginProvider" text NOT NULL, "Name" text NOT NULL, "Value" text NULL, CONSTRAINT "PK_AspNetUserTokens" PRIMARY KEY ("UserId", "LoginProvider", "Name"), CONSTRAINT "FK_AspNetUserTokens_AspNetUsers_UserId" FOREIGN KEY ("UserId") REFERENCES "AspNetUsers" ("Id") ON DELETE CASCADE ); CREATE INDEX "IX_AspNetRoleClaims_RoleId" ON "AspNetRoleClaims" ("RoleId"); CREATE UNIQUE INDEX "RoleNameIndex" ON "AspNetRoles" ("NormalizedName") WHERE [NormalizedName] IS NOT NULL; CREATE INDEX "IX_AspNetUserClaims_UserId" ON "AspNetUserClaims" ("UserId"); CREATE INDEX "IX_AspNetUserLogins_UserId" ON "AspNetUserLogins" ("UserId"); CREATE INDEX "IX_AspNetUserRoles_RoleId" ON "AspNetUserRoles" ("RoleId"); CREATE INDEX "EmailIndex" ON "AspNetUsers" ("NormalizedEmail"); CREATE UNIQUE INDEX "UserNameIndex" ON "AspNetUsers" ("NormalizedUserName") WHERE [NormalizedUserName] IS NOT NULL; INSERT INTO "__EFMigrationsHistory" ("MigrationId", "ProductVersion") VALUES ('20180514204732_initial', '2.0.3-rtm-10026');

Please post your complete CREATE FUNCTION statement. Error code 42601 refers to some syntax error which probably occurs just before you start looping. To find it I need to see more code.
Please don’t mail me directly. Only mail to pgsql-sql(at)postgresql(dot)org

>>> «Ezequias R. da Rocha» <ezequias(at)fastcon(dot)com(dot)br> 2007-03-13 14:19 >>>
They are not working well.

Allways the same error.

(if while)
ERROR: syntax error at or near «WHILE»
SQL state: 42601
Character: 1

(if for)

ERROR: syntax error at or near «FOR»
SQL state: 42601
Character: 1

(if loop)
ERROR: syntax error at or near «LOOP»
SQL state: 42601
Character: 1
I installed the pgsql but I don’t think it is a problem of language.
What could be wrong ? I am not using functions too. I am using the SQL
tool of PgAdmin III.

Any help would be glad.

Ezequias

Bart Degryse escreveu:
> As you can see in the manual
> (http://www.postgresql.org/docs/8.1/interactive/plpgsql-control-structures.html)
> you have to remove the ; after the initial LOOP
>
> LOOP
> statements
> IF i > 1000000 THEN EXIT;
> END LOOP;
>
> Or you can try…
>
> WHILE i <= 1000000 LOOP
> statements
> END LOOP;
>
> Or even… (don’t initialize i in this case)
>
> FOR i IN 1 .. 1000000 LOOP
> statements
> END LOOP;
>
> >>> «Ezequias R. da Rocha» <ezequias(at)fastcon(dot)com(dot)br> 2007-03-13 14:00 >>>
> Ezequias R. da Rocha escreveu:
> > Hi list,
> >
> > I would like to test the power of processing of postgresql (and my
> > server) by insert a large ammount of data on a table I have.
> >
> > I would like to know how to implement a For… loop . With it I think
> > I could check the real power of my server.
> >
> > Ezequias
> >
> I tryed a way but it is not working. Please see it:
>
> LOOP;
> — some computations
> insert into carga (desc) values (‘My Text’);
> IF count > 1000000 THEN
> EXIT; — exit loop
> END IF;
> END LOOP;
>
> The postgresql reports the following error:
> /
> ERROR: syntax error at or near «LOOP»
> SQL state: 42601
> Character: 1/
>
> —————————(end of broadcast)—————————
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq