Error mismatched parentheses at or near

plpgsql Cursor Mismatched Parentheses

Subject: plpgsql Cursor Mismatched Parentheses Date: 2002-07-12 08:32:36 Message-ID: 200207120832.24c4@th00.opsion.fr Views: Raw Message | Whole Thread | Download mbox | Resend email Thread: Lists: pgsql-general

I have never successfully used bounded parameterized
cursors. Error occurs when opening cursor. Please see
below and help!

CN
===========================
CREATE FUNCTION test(SMALLINT,SMALLINT) RETURNS
BOOLEAN AS ‘
DECLARE
y1 ALIAS FOR $1;
m1 ALIAS FOR $2;

o TEXT;
y2 SMALLINT;
m2 SMALLINT;

c CURSOR (o TEXT,y1 SMALLINT,m1 SMALLINT,y2
SMALLINT,m2 SMALLINT) IS
SELECT column1,column2 FROM table1 WHERE column9=o
AND column3 BETWEEN (SELECT column3 FROM table1 WHERE
column9=o AND column1=y1 AND column2=m1) AND (SELECT
column3 FROM table1 WHERE column9=o AND column1=y2
AND column2=m2) ORDER BY 1,2;

OPEN c(o,y1,m1,y2,m2);
CLOSE c;

RETURN TRUE;
END;’ LANGUAGE ‘plpgsql’;
======================
database=# select test(2002,5);
NOTICE: plpgsql: ERROR during compile of test near
line 17
ERROR: mismatched parentheses

Responses

• Re: plpgsql Cursor Mismatched Parentheses at 2002-07-12 13:39:16 from Tom Lane

Browse pgsql-general by date

Copyright © 1996-2023 The PostgreSQL Global Development Group

Источник

plv8 disadvantages or limitations?

I’m playing around with PLV8 to write trigger and stored procedures for PostgreSQL. So far I don’t really see disadvantages compared to PLPGSQL. Especially if working with JSON it seems even smarter then PLPGSQL. Are there known disadvantages or limitations if using PLV8? Can PLV8 be a full replacement for PLPGSQL? It would be great if somebody could share his experience on this.

3 Answers 3

The advantages and disadvantages of PLV8 are same as advantages and disadvantages of PLPerl, PLPython and other PL languages.

• It is not integrated with PostgreSQL engine — the processing SQL statements result can be slower. PLpgSQL is fully integrated to PostgreSQL engine.
• SQL is not integrated to language — isn’t possible to do static analyse of embedded SQL. It is possible with PLpgSQL — see plpgsql_check.
• Can do better expensive mathematical calculations, a manipulations with strings and arrays are usually faster than in PLpgSQL.
• Can use libraries developed for languages — Perl — CPAN, .
• JavaScript, Perl, Python are generic languages — so any generic tasks are implemented there well.
• PLpgSQL is mature language designed for manipulation with data in relation database environment. Almost all what developer needs for data work with data is there. The iteration over result, taking data from database needs less more readable code.

PLpgSQL is perfect language for data manipulation via SQL language. Other PL is better for anything else — IO, Network, special formatting, slow numeric calculations, .

Источник

error:mismatched parentheses

I have a problem regarding stored procedure, it always informs me that there
is ERROR: mismatched parentheses after executing, If anyone can take a look
to see what i’m doing wrong :

create or replace function test(TEXT, INTEGER, TEXT, TEXT) returns refcursor
AS ‘
DECLARE
refCur REFCURSOR;
recOrdOne RECORD;
BEGINA
OPEN refCur FOR
SELECT fltime, network, port, tooctets, fromoctets,
sum(tooctets + fromoctets) AS sumoctets
FROM (SELECT distinct tariffs.port AS
port FROM tariffs WHERE tariffs.custid = $2 ) AS keys
LEFT OUTER JOIN (
SELECT fltime, network, tariffs.port AS port, sum(fld_tooctets) AS
tooctets, sum(fld_fromoctets) AS fromoctets, sum(fld_tooctets +
fld_fromoctets) AS sumoctets
FROM day_log, networks, tariffs, services
WHERE networks.custid = $2
AND day_log.custid = $2
AND tariffs.custid = $2
AND tariffs.tariff = networks.tariff
AND (FOR recOrdOne IN SELECT distinct fltime FROM day_log WHERE fltime
>= $3 AND fltime

Copyright © 1996-2023 The PostgreSQL Global Development Group

Источник

Усечение всех таблиц в базе данных Postgres

Мне нужно регулярно удалять все данные из моей базы данных PostgreSQL перед перестройкой. Как я могу сделать это непосредственно в SQL?

В настоящий момент мне удалось найти инструкцию SQL, которая возвращает все команды, которые мне нужно выполнить:

Но я не вижу способа выполнить их программно, как только я их получу.

9 ответов

FrustratedWithFormsDesigner корректен, PL/pgSQL может это сделать. Здесь script:

Это создает хранимую функцию (вам нужно сделать это только один раз), которую вы можете использовать следующим образом:

Явные курсоры редко нужны в plpgsql. Просто используйте более простой и быстрый неявный курсор цикла FOR :

Примечание. Так как имена таблиц не уникальны для каждой базы данных, вы должны убедиться в правильности имен таблиц. Кроме того, я ограничиваю функцию по умолчанию «общедоступной». Адаптируйте к вашим потребностям, но обязательно исключите системные схемы pg_* и information_schema .

Будьте очень осторожны с этими функциями. Они уничтожают вашу базу данных. Я добавил устройство безопасности для детей. Прокомментируйте строку RAISE NOTICE и раскомментируйте EXECUTE чтобы запустить бомбу.

format() требует Postgres 9.1 или новее. В более старых версиях конкатенация строки запроса выглядит так:

Единая команда, без циклы

Поскольку мы можем TRUNCATE несколько таблиц, нам вообще не нужен курсор или петля:

Совокупность всех имен таблиц и выполнение одного оператора. Проще, быстрее:

Уточненный запрос

Вам даже не нужна функция. В Postgres 9. 0+ вы можете выполнять динамические команды в инструкции DO . А в Postgres 9. 5+ синтаксис может быть еще проще:

О разнице между pg_class , pg_tables и information_schema.tables :

О именах regclass и цитируемых таблиц:

Для повторного использования

Может быть проще и (намного) быстрее создать базу данных «шаблона» (пусть назовите ее my_template ) с помощью вашей структуры ванили и всех пустых таблиц. Затем выполните цикл DROP / CREATE DATABASE :

Это очень быстро, потому что Postgres копирует всю структуру на уровне файла. Отсутствие проблем с параллелизмом или другие накладные расходы замедляют работу.

Источник

Regex for checking if a string has mismatched parentheses?

In a PHP script, what regex should I use to check for mismatched parentheses in a string? Things that I want to allow include:

Things I want to prevent:

• This is )bad(
• This is also (bad
• This is (bad (too)

Update: You guys all rock. Doing this with a regex seemed trickier than it should have, and these kinds of 2nd level answers are what makes stackoverflow beautiful. Thanks for the links and the pseudocode. I’m not sure who to give the answer to, so I apologize to everyone whose answers I can’t accept.

8 Answers 8

Regex is not the right tool for the job. Scan a string manually.

You can do this with a regular expression — PCRE, as used by PHP, allows recursive patterns. The PHP Manual gives an example that is almost exactly what you want:

This matches any correctly parenthesised substring as long as it begins and ends with parentheses. If you want to ensure the entire string is balanced, allowing strings like «wiggedy(wiggedy)(wiggedy(wack))», here’s what I came up with:

Here’s an explanation of the pattern, which may be more illuminating than obfuscatory:

There are lots of considerations of efficiency and correctness that come up with these sorts of regexes. Be careful.

It is not possible to accomplish this with a regex. Brace matching requires a recursive/counting feature that is not available in a regex. You’ll need a parser for this.

Your examples don’t include any nested parentheses… if you aren’t concerned with nesting, then this can be done using the following expression:

This will match against all the strings in your «allow» list and fail against the strings in your «prevent» list. However, it will also fail against any string with nested parentheses. e.g. «this (is (not) ok)»

As others have already pointed out, regular expressions are not the correct tool if you need to handle nesting.

Agree with the fact that this is impossible with a REGEX. You could do the following, though:

To extend JaredPar’s answer, it’s not very difficult to solve without using a regex, just write a function that examines each character in the string and increments/decrements a counter. If you find a «(«, increment it, and if you find a «)», decrement it. If the counter ever goes below 0, you can break, the string is invalid. When you’ve processed the whole string, if the counter isn’t 0, there was an unmatched open parenthesis.

Why it’s not possible with a regex

The other answers are all correct, but I just want to put in a plug for theoretical computer science. this is a case where knowing the theory gives an actual practical advantage.

A regex corresponds to a deterministic finite automaton (DFA), but paren matching require a context-free grammar, which can be realized as a finite automaton (PDA) but not by a DFA.

Because of this, without a lot of extra brain-work, we know that the answer is no, and we don’t have to worry that there is something we’re just overlooking. So, you can be confident in the above answers, and not worry that the authors are just overlooking something when they give that answer.

Almost all compiler books will talk about this, here’s a quick overview:

Источник

DROP FUNCTION IF EXISTS f_multi_union(text);

CREATE OR REPLACE FUNCTION f_multi_union(new_tab_name text)
  RETURNS Table (my_id int, metric double precision, geom geometry)
  LANGUAGE plpgsql AS
$func$
BEGIN
   RETURN QUERY EXECUTE
   (
   DROP TABLE IF EXISTS working.'' || new_tab_name || '';
   CREATE TABLE working.'' || new_tab_name || '' AS (
   SELECT string_agg(format('SELECT * FROM %s', tbl), ' UNION ALL ')
   FROM (SELECT tbl FROM working.tablelist) sub 
    )
   );
END
$func$;

1 ответ

Лучший ответ

DROP FUNCTION IF EXISTS f_multi_union(text);

CREATE OR REPLACE FUNCTION f_multi_union(new_tab_name text)
  RETURNS void -- nothing to return
  LANGUAGE plpgsql AS
$func$
DECLARE
    _sql            TEXT;
BEGIN
    _sql    := format('DROP TABLE IF EXISTS working.%I;', new_tab_name); -- avoid SQL injection
    EXECUTE _sql;

    _sql    :=  'SELECT string_agg(format(''SELECT * FROM %I'', tbl), '' UNION ALL '')
                FROM (SELECT tbl FROM working.tablelist) sub;';
    EXECUTE _sql 
        INTO _sql; -- overwrite current _sql content

    _sql    := format('CREATE TABLE working.%I AS %s;', new_tab_name, _sql);
    EXECUTE _sql;
   
END
$func$;

Hi!

I have never successfully used bounded parameterized
cursors. Error occurs when opening cursor. Please see
below and help!

Regards,

CN
===========================
CREATE FUNCTION test(SMALLINT,SMALLINT) RETURNS
BOOLEAN AS ‘
DECLARE
y1 ALIAS FOR $1;
m1 ALIAS FOR $2;

o TEXT;
y2 SMALLINT;
m2 SMALLINT;

c CURSOR (o TEXT,y1 SMALLINT,m1 SMALLINT,y2
SMALLINT,m2 SMALLINT) IS
SELECT column1,column2 FROM table1 WHERE column9=o
AND column3 BETWEEN (SELECT column3 FROM table1 WHERE
column9=o AND column1=y1 AND column2=m1) AND (SELECT
column3 FROM table1 WHERE column9=o AND column1=y2
AND column2=m2) ORDER BY 1,2;

BEGIN
o:=»xxx»;
y2:=2002;
m2=7;

OPEN c(o,y1,m1,y2,m2);
CLOSE c;

RETURN TRUE;
END;’ LANGUAGE ‘plpgsql’;
======================
database=# select test(2002,5);
NOTICE: plpgsql: ERROR during compile of test near
line 17
ERROR: mismatched parentheses

———————————————————
You too can have your own email address from Eurosport.
http://www.eurosport.com

Явные курсоры редко нужны в plpgsql. Просто используйте более простой и быстрый неявный курсор цикла FOR:

Примечание. Так как имена таблиц не уникальны для каждой базы данных, вы должны убедиться в правильности имен таблиц. Кроме того, я ограничиваю функцию по умолчанию «общедоступной». Адаптируйте к вашим потребностям, но обязательно исключите системные схемы pg_* и information_schema.

Будьте очень осторожны с этими функциями. Они уничтожают вашу базу данных. Я добавил устройство безопасности для детей. Прокомментируйте строку RAISE NOTICE и раскомментируйте EXECUTE чтобы запустить бомбу…

CREATE OR REPLACE FUNCTION f_truncate_tables(_username text)
  RETURNS void AS
$func$
DECLARE
   _tbl text;
   _sch text;
BEGIN
   FOR _sch, _tbl IN 
      SELECT schemaname, tablename
      FROM   pg_tables
      WHERE  tableowner = _username
      AND    schemaname = 'public'
   LOOP
      RAISE NOTICE '%',
      -- EXECUTE  -- dangerous, test before you execute!
         format('TRUNCATE TABLE %I.%I CASCADE', _sch, _tbl);
   END LOOP;
END
$func$ LANGUAGE plpgsql;

format() требует Postgres 9.1 или новее. В более старых версиях конкатенация строки запроса выглядит так:

'TRUNCATE TABLE ' || quote_ident(_sch) || '.' || quote_ident(_tbl)  || ' CASCADE';

Единая команда, без циклы

Поскольку мы можем TRUNCATE несколько таблиц, нам вообще не нужен курсор или петля:

• Передача имен таблиц в массиве

Совокупность всех имен таблиц и выполнение одного оператора. Проще, быстрее:

CREATE OR REPLACE FUNCTION f_truncate_tables(_username text)
  RETURNS void AS
$func$
BEGIN
   RAISE NOTICE '%', 
   -- EXECUTE  -- dangerous, test before you execute!
  (SELECT 'TRUNCATE TABLE '
       || string_agg(format('%I.%I', schemaname, tablename), ', ')
       || ' CASCADE'
   FROM   pg_tables
   WHERE  tableowner = _username
   AND    schemaname = 'public'
   );
END
$func$ LANGUAGE plpgsql;

Вызов:

SELECT truncate_tables('postgres');

Уточненный запрос

Вам даже не нужна функция. В Postgres 9. 0+ вы можете выполнять динамические команды в инструкции DO. А в Postgres 9. 5+ синтаксис может быть еще проще:

DO
$func$
BEGIN
   RAISE NOTICE '%', 
   -- EXECUTE
   (SELECT 'TRUNCATE TABLE ' || string_agg(oid::regclass::text, ', ') || ' CASCADE'
    FROM   pg_class
    WHERE  relkind = 'r'  -- only tables
    AND    relnamespace = 'public'::regnamespace
   );
END
$func$;

О разнице между pg_class, pg_tables и information_schema.tables:

• Как проверить, существует ли таблица в данной схеме

О именах regclass и цитируемых таблиц:

• Имя таблицы как параметр функции PostgreSQL

Для повторного использования

Может быть проще и (намного) быстрее создать базу данных «шаблона» (пусть назовите ее my_template) с помощью вашей структуры ванили и всех пустых таблиц. Затем выполните цикл DROP/ CREATE DATABASE:

DROP DATABASE mydb;
CREATE DATABASE mydb TEMPLATE my_template;

Это очень быстро, потому что Postgres копирует всю структуру на уровне файла. Отсутствие проблем с параллелизмом или другие накладные расходы замедляют работу.

PostgreSQL online editor

Write, Run & Share PostgreSQL queries online using OneCompiler’s PostgreSQL online editor and compiler for free. It’s one of the robust, feature-rich online editor and compiler for PostgreSQL. Getting started with the OneCompiler’s PostgreSQL editor is really simple and pretty fast. The editor shows sample boilerplate code when you choose database as ‘PostgreSQL’ and start writing queries to learn and test online without worrying about tedious process of installation.

About PostgreSQL

PostgreSQL is a open source relational database system and is also knows as Postgres.

Key Features:

• Postgres is not only free and open-source but also it is highly extensible.
• Custom Data types and funtions from various programming languaues can be introduced and the good part is compiling entire database is not required.
• ACID(Atomicity, Consistency, Isolation, Durability) compliant.
• First DBMS which implemented Multi-version concurrency control (MVCC) feature.
• It’s the default database server for MacOS.
• It supports all major operating systems like Linux, Windows, OpenBSD,FreeBSD etc.

Syntax help

1. CREATE

CREATE command is used to create a table, schema or an index.

Syntax:

         CREATE TABLE table_name (
                column1 datatype,
                column2 datatype,
                ....);

2. ALTER

ALTER command is used to add, modify or delete columns or constraints from the database table.

Syntax

ALTER TABLE Table_name ADD column_name datatype;

3. TRUNCATE:

TRUNCATE command is used to delete the data present in the table but this will not delete the table.

Syntax

TRUNCATE table table_name;

4. DROP

DROP command is used to delete the table along with its data.

Syntax

DROP TABLE table_name;

5. RENAME

RENAME command is used to rename the table name.

Syntax

ALTER TABLE table_name1 RENAME to new_table_name1; 

6. INSERT

INSERT Statement is used to insert new records into the database table.

Syntax

INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);

7. SELECT

Select statement is used to select data from database tables.

Syntax:

SELECT column1, column2, ...
FROM table_name; 

8. UPDATE

UPDATE statement is used to modify the existing values of records present in the database table.

Syntax

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition; 

9. DELETE

DELETE statement is used to delete the existing records present in the database table.

Syntax

DELETE FROM table_name where condition;