Postgresql declare ошибка синтаксиса

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,

CREATE OR REPLACE FUNCTION prc_tst_bulk(sql text)
RETURNS TABLE (name text, rowcount integer) AS
$$
BEGIN
WITH m_ty_person AS (return query execute sql)
select name, count(*) from m_ty_person where name like '%a%' group by name
union
select name, count(*) from m_ty_person where gender = 1 group by name;
END
$$ LANGUAGE plpgsql;

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

ERROR: syntax error at or near "return"
LINE 5: WITH m_ty_person AS (return query execute sql)

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,

RETURN QUERY EXECUTE '
WITH m_ty_person AS (' || sql || $x$)
SELECT name, count(*)::int FROM m_ty_person WHERE name LIKE '%a%' GROUP BY name
UNION
SELECT name, count(*)::int FROM m_ty_person WHERE gender = 1 GROUP BY name$x$;

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.

var google_conversion_label = «owonCMyG5nEQ0aD71QM»;

2 ответы

Не тот ответ, который вы ищете? Просмотрите другие вопросы с метками

postgresql

or задайте свой вопрос.

• Remove From My Forums

• Question

• Hello Guys,

  I’m getting the following error from the code below:

  Msg 156, Level 15, State 1, Procedure RemoveContainer, Line 38
  Incorrect syntax near the keyword ‘DECLARE’.
  Msg 156, Level 15, State 1, Procedure RemoveContainer, Line 50
  Incorrect syntax near the keyword ‘OPEN’.

  The declare instruction causing the problem is the following

  DECLARE container_cursor CURSOR FOR

  Can somebody help me spot the problem please?

  Here the full script

  CREATE PROCEDURE [dbo].RemoveContainer
  (
    @containerId bigint
  )
  AS
  
  DECLARE @fileId bigint;
  DECLARE @fileName nvarchar(50);
  DECLARE @lastModified datetime;
  DECLARE @parentId bigint;
  DECLARE @folderId bigint;
  DECLARE @blockId bigint;
  DECLARE @isFile bit;
  
  with cte (ID ,Name , LastModified ,  ParentID, FolderID )
  AS
   (
  	 SELECT
    	 ID
  		 ,Name 
  		 ,GETDATE ()
  		 ,ParentID
  		 ,ID as FolderID
  	 FROM 
  	  sqlfolder 
  	 WHERE id = @containerId
  	 UNION ALL
  	 SELECT c.ID, c.Name ,GETDATE () , c.ParentID, c.ID as FolderID
  	 FROM cte p
  	 INNER JOIN SqlFolder c ON p.ID = c.ParentID
  )
  
  DECLARE container_cursor CURSOR FOR 
  SELECT f.ID , f.Name, f.LastModified, cte.ParentID , f.FolderID , 1 as IsfFile
  FROM (      
  			SELECT *, 0 AS BlockID , 0 AS IsFile
  			FROM cte
  			UNION ALL
  			SELECT f.ID , f.Name, f.LastModified, cte.ParentID , f.FolderID , SqlFileBlock.BlockID , 1 as IsfFile 
  			FROM SqlFile f 
  			INNER JOIN SqlFileBlock ON SqlFileBlock.FileID = f.ID
  			INNER JOIN cte ON f.FolderID = cte.ID     
     )
  
  OPEN container_cursor;
  FETCH NEXT FROM container_cursor 
  INTO @fileId, @fileName, @lastModified, @parentId, @folderId, @blockId, @isFile;
    
  DECLARE @blockRefCount bigint
    
  WHILE @@FETCH_STATUS = 0
  BEGIN
     
     SELECT @blockRefCount = COUNT(*)
     FROM SqlFileBlock
     WHERE SqlFileBlock.BlockID = @blockId
     
     IF ( @blockRefCount = 1)
     BEGIN
      DELETE
      FROM SqlBlock 
      WHERE SqlBlock.ID = @blockId
     END
     
     DELETE
     FROM SqlFileBlock
     WHERE SqlFileBlock.FileID = @fileId
       
     FETCH NEXT FROM container_cursor 
     INTO @fileId, @fileName, @lastModified, @parentId, @folderId, @blockId, @isFile
  END
    
  CLOSE container_cursor;
  DEALLOCATE container_cursor;
  
  

  THanks for any help.

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');