Error syntax error at or near declare

PostgreSQL error 42601 mainly occurs due to the syntax errors in the code. Proper syntax check and code correction will fix it up.

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.

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.

GET STARTED

var google_conversion_label = «owonCMyG5nEQ0aD71QM»;

Источник

Ошибка синтаксиса ошибки postgres в или около «int» при создании функции

Я новичок в postgres. Я получил эту ошибку при попытке запустить следующий скрипт:

CREATE OR REPLACE FUNCTION xyz(text) RETURNS INTEGER AS
'DECLARE result int;
BEGIN
    SELECT count(*) into result from tbldealercommissions
    WHERE 
    txtdealercode = $1;

    if result < 1 then returns 1; 
    else returns 2 ;
    end if;
END;
    '
LANGUAGE sql VOLATILE;

Ошибка 

ERROR:  syntax error at or near "int"
LINE 3: 'DECLARE result int;

не уверен, что вызывает эту ошибку. Любая помощь приветствуется.

2 ответы

Это не подходит: 

LANGUAGE sql

используйте это вместо:

LANGUAGE plpgsql

Синтаксис, который вы пытаетесь использовать, — это не чистый язык SQL, а процедурный язык PL / pgSQL. В PostgreSQL вы можете устанавливать разные языки, и PL / pgSQL является в этом отношении только первыми. Это также означает, что вы можете получить сообщение об ошибке, что этот язык не установлен. В этом случае используйте 

CREATE LANGUAGE plpgsql;

который его активизирует. В зависимости от версии PostgreSQL для выполнения этого шага вам могут потребоваться права суперпользователя.

Удачи.

ответ дан 06 окт ’11, 14:10

Вы не только используете неправильный язык (как отмечает AH), но и returns ключевое слово, вы хотите return. Возможно, вы захотите использовать другой разделитель, чтобы избежать проблем со строковыми литералами в ваших функциях, $$ довольно часто. Я думаю, ваша функция должна выглядеть примерно так:

CREATE OR REPLACE FUNCTION xyz(text) RETURNS INTEGER AS $$
DECLARE result int;
BEGIN
    select count(*) into result
    from tbldealercommissions
    where txtdealercode = $1;

    if result < 1 then return 1; 
    else return 2;
    end if;
END;
$$ LANGUAGE plpgsql VOLATILE;

ответ дан 06 окт ’11, 18:10

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

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.

Answers

  • New code is much shorter and easier to understand. Try

    with AllFolders (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 AllFolders p
			 INNER JOIN SqlFolder c ON p.ID = c.ParentID		
		) 						 		

SELECT * INTO #TempTable
FROM AllFolders

DELETE
FROM SqlFile WHERE EXISTS (select 1 from #TempTable
WHERE SqlFile.FolderID = #TempTable.ID)

    Also, if you don’t need to use #TempTable later in your query, you can skip the second SELECT * and use CTE directly in the DELETE statement the same way as I showed but using CTE instead of #TempTable.

    For every expert, there is an equal and opposite expert. — Becker’s Law

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog

    • Marked as answer by

      Monday, April 4, 2011 7:47 PM

Источник

  Last tested: Feb 2021

Overview

This SQL error generally means that somewhere in the query, there is invalid syntax.
Some common examples:

  • Using a database-specific SQL for the wrong database (eg BigQuery supports DATE_ADD, but Redshift supports DATEADD)
  • Typo in the SQL (missing comma, misspelled word, etc)
  • Missing a sql clause (missed from, join, select, etc)
  • An object does not exist in the database or is not accessible from the current query (eg referencing orders.id when there is no orders table joined in the current query, etc)

In some circumstances, the database error message may display extra detail about where the error was raised, which can be helpful in narrowing down where to look.

Error Message

SQL ERROR: syntax error at or near

Troubleshooting

This should generally be the first step to troubleshoot any SQL syntax error in a large query: iteratively comment out blocks of SQL to narrow down where the problem is.

TIP: To make this process easier, change the group by clause to use position references
eg: group by 1,2,3,4,5 instead of group by orders.status, orders.date, to_char(...)...
as well as separate the where and having clauses onto multiple lines.

So for example, say we have the following query:

play_arrow

WITH cte AS (
select id, status, sales_amountfrom orders
)
select status, foo.date, sum(cte.sales_amount), count(*) from cte
join foo on cte.date = foo.date
group by status, foo.date
order by 3 desc

We could start by running just the portion in the CTE:

play_arrow

-- WITH cte AS (
select id, status, sales_amountfrom orders
-- )
-- select status, foo.date, sum(cte.sales_amount), count(*)
-- from cte
-- join foo on cte.date = foo.date
-- group by 1, 2
-- order by 3 desc

Then strip out the aggregates and portions related to them

play_arrow

WITH cte AS (
    select id, status, sales_amountfrom orders
)
select status, foo.date, -- sum(cte.sales_amount), count(*)
from cte
join foo on cte.date = foo.date
-- group by 1, 2
-- order by 3 desc

Iteratively stripping out / adding back in portions of the query until you find the minimum query to trigger the error.

  • Lookup functions and syntax If the query is small enough, or if we’ve narrowed the scope enough with 1, google all the functions used in the query and verify that they exist and are being used correctly.

  • Verify all objects exist Verify that you’ve joined all tables used in the select, where, and having clause, and that those tables exist in the db. Once we’ve narrowed things down from 1, also check that each column exists in the table specified.

Источник

@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');
Источник

Понравилась статья? Поделить с друзьями:
  • Error syntax error at or near call
  • Error syncing pod skipping
  • Error synchronizing data with database
  • Error synchronizing after initial wipe timed out waiting for object
  • Error sync firefox