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
