Search Postgresql Archives

View definition changes after reloading pg_dump export

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 




Hello all,

For our application we are generating a template.sql file with our schema in it by using pg_dump to a template.sql file. When we apply a DB change we load this template into a new database, apply the changes and export it again. This works, but we run into a small issue recently where we see a weird diff showing up in our template.sql after regenerating the schema.

There is a view that we create and it uses a function and the view definition changes between runs. I'm not sure why this is happening, does someone know? This is all done with Postgres 12. Both versions 12.12 and 12.11 exhibit this behaviour.

The reproduction path is down below.

Our upgrade script defines a select myfunction as seen here:

```
SELECT myfunc(
  hstore(ARRAY['bar', 'baz'], ARRAY[ foo.bar, foo.baz])
)
```

When we run the SQL and export the database schema this becomes:

```
SELECT myfunc(hstore(ARRAY['bar'::text, 'baz'::text], ARRAY[foo.bar, foo.baz::character varying]::text[])
```

When we create a new database with the resulting template.sql, the definition becomes:

```
SELECT myfunc(hstore(ARRAY['bar'::text, 'baz'::text], ARRAY[foo.bar::text, foo.baz::character varying::text])) AS sometext
```

And this is the final form, shouldn't the first iteration of the view definition be this final form?

Cheers,
Wesley


The reproduction path:

```
CREATE EXTENSION IF NOT EXISTS "hstore";

DROP VIEW IF EXISTS foo_view;
DROP FUNCTION IF EXISTS myfunc cascade;
DROP TABLE IF EXISTS foo;

CREATE table foo (
  bar varchar(50),
  baz text,
  here text
);

CREATE OR REPLACE FUNCTION myfunc(IN foo public.hstore, OUT sometext text)
RETURNS text LANGUAGE plpgsql AS $$
    DECLARE
        bar text;
        baz text;
    BEGIN
        bar := foo->'bar';
        baz := foo->'baz';
        sometext := concat(bar, ' ', baz);
    END;
$$;

CREATE VIEW foo_view AS (
    SELECT myfunc(
        hstore(ARRAY['bar', 'baz'], ARRAY[ foo.bar, foo.baz])
    ) AS sometext
    FROM foo
);

\d+ foo_view
-- yields as View definition
-- SELECT myfunc(hstore(ARRAY['bar'::text, 'baz'::text], ARRAY[foo.bar, foo.baz::character varying]::text[])) AS sometext
--   FROM foo;

CREATE VIEW foo_view_copy AS (
SELECT myfunc(hstore(ARRAY['bar'::text, 'baz'::text], ARRAY[foo.bar, foo.baz::character varying]::text[])) AS sometext
   FROM foo
);

\d+ foo_view_copy
-- yields as View definition
-- SELECT myfunc(hstore(ARRAY['bar'::text, 'baz'::text], ARRAY[foo.bar::text, foo.baz::character varying::text])) AS sometext
--   FROM foo;
```

--
Wesley Schwengle, Developer
xxllnc Zaaksysteem, https://www.zaaksysteem.nl






[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux