PL/pgSQL CREATE или REPLACE в рамките на EXECUTE

Имам следния скрипт за динамично създаване на изгледи в база данни на PostgreSQL.

CREATE OR REPLACE FUNCTION cs_refresh_mviews() RETURNS integer AS $$
DECLARE
    mviews RECORD;
    query text;
    park_name text;
    ppstatements int;
BEGIN
    RAISE NOTICE 'Creating views...';

    FOR mviews IN SELECT name FROM "Canadian_Parks" LOOP
        park_name := mviews.name;
        RAISE NOTICE 'Creating or replace view %s...', mviews.name; 
        query := 'CREATE OR REPLACE VIEW %_view AS
          SELECT * from "Canadian_Parks" where name=''%'';
          ALTER TABLE %_view OWNER TO postgres', park_name, park_name, park_name;
      --  RAISE NOTICE query;
        EXECUTE query;
    END LOOP;

    RAISE NOTICE 'Done refreshing materialized views.';
    RETURN 1;
END;
$$ LANGUAGE plpgsql;

Потвърдих целостта на низа, като напр

CREATE OR REPLACE VIEW Saguenay_St__Lawrence_view AS
SELECT * from "Canadian_Parks" where name='Saguenay_St__Lawrence';
ALTER TABLE Saguenay_St__Lawrence_view OWNER TO postgres

присвоени на променливата на заявката чрез ръчно изпращане на това в базата данни и получаване на успешен отговор.

Ако обаче се опитам да изпълня функцията с помощта на

SELECT cs_refresh_mviews();

показва се следната грешка:

ERROR:  query "SELECT 'CREATE OR REPLACE VIEW %_view AS SELECT * from "Canadian_Parks" where name=''%''; ALTER TABLE %_view OWNER TO postgres', park_name, park_name, park_name" returned 4 columns
CONTEXT:  PL/pgSQL function "cs_refresh_mviews" line 32 at assignment

********** Error **********

ERROR: query "SELECT 'CREATE OR REPLACE VIEW %_view AS SELECT * from "Canadian_Parks" where name=''%''; ALTER TABLE %_view OWNER TO postgres', park_name, park_name, park_name" returned 4 columns
SQL state: 42601
Context: PL/pgSQL function "cs_refresh_mviews" line 32 at assignment

Защо това е преобразувано в оператор SELECT, вместо в чист CREATE?


postgresql dynamic-sql materialized-views plpgsql
person Klaus Nji    schedule 09.10.2012    source източник

Отговори (2)


arrow_upward
4
arrow_downward

Настройката ви е доста объркана. Защо бихте запазили част от името на изглед в съставен тип на таблица, вместо да го запазите в колона с обикновен текст?

Както и да е, може да работи така:

Съответстващ въпрос за настройка:

CREATE SCHEMA x;  -- demo in test schema
SET search_path = x;
CREATE TYPE mviews AS (id int, name text); -- composite type used in table

CREATE TABLE "Canadian_Parks" (name mviews);
INSERT INTO "Canadian_Parks"(name) VALUES
 ('(1,"canadian")')
,('(2,"islandic")');  -- composite types, seriously?

SELECT name, (name).* from "Canadian_Parks";

CREATE OR REPLACE FUNCTION cs_refresh_mviews()
  RETURNS int LANGUAGE plpgsql SET search_path = x AS  -- search_path for test
$func$
DECLARE
    _parkname text;
BEGIN

FOR _parkname IN SELECT (name).name FROM "Canadian_Parks" LOOP
   EXECUTE format('
      CREATE OR REPLACE VIEW %1$I AS
      SELECT * FROM "Canadian_Parks" WHERE (name).name = %2$L;
      ALTER TABLE %1$I OWNER TO postgres'
      , _parkname || '_view', _parkname);
END LOOP;

RETURN 1;

END
$func$;

SELECT cs_refresh_mviews();

DROP SCHEMA x CASCADE; -- clean up

Основни точки

  • Тъй като изпълнявате текст с изпълнение, вие трябва да се предпазите срещу SQL инжектиране. Използвам функцията format() за < em>идентификатори и литерала

  • Използвам синтаксиса SELECT (name).name, за да се справя с вашата странна настройка и да извлека name, от който се нуждаем веднага.

  • По същия начин, VIEW трябва да прочете WHERE (name).name = .., за да работи в тази настройка.

  • Премахнах много шум, който няма отношение към въпроса.

  • Също така вероятно е безсмислено да има функция RETURN 1. Просто дефинирайте функцията с RETURNS void. Запазих го обаче, за да отговаря на въпроса.

Разплетена настройка

Как вероятно трябва да бъде:

CREATE SCHEMA x;
SET search_path = x;

CREATE TABLE canadian_parks (id serial primary key, name text);
INSERT INTO canadian_parks(name) VALUES ('canadian'), ('islandic');

SELECT * from canadian_parks;

CREATE OR REPLACE FUNCTION cs_refresh_mviews()
  RETURNS void LANGUAGE plpgsql SET search_path = x AS
$func$
DECLARE
    parkname text;
BEGIN

FOR parkname IN SELECT name FROM canadian_parks LOOP
   EXECUTE format('
      CREATE OR REPLACE VIEW %1$I AS
      SELECT * FROM canadian_parks WHERE name = %2$L;
      ALTER TABLE %1$I OWNER TO postgres'
      , parkname || '_view', parkname);
END LOOP;

END
$func$;

SELECT cs_refresh_mviews();

DROP SCHEMA x CASCADE;
person Erwin Brandstetter    schedule 10.10.2012

arrow_upward
2
arrow_downward

Не сте разбрали правилно използването на запетаи в израза за присвояване. Превръща query в масив (RECORD) вместо в скалар. Използвайте конкатенация:

park_name := quote_ident(mviews.name||'_view');
query := 'CREATE OR REPLACE VIEW '||park_name||' AS SELECT * from "Canadian_Parks" where name='||quote_literal(mviews.name)||'; ALTER TABLE '||park_name||' OWNER TO postgres';
person SadSamurai    schedule 09.10.2012