-- Convert schema '/home/kes/work/projects/tucha/monkeyman/share/migrations/_source/deploy/138/001-auto.yml' to '/home/kes/work/projects/tucha/monkeyman/share/migrations/_source/deploy/139/001-auto.yml':;
;
BEGIN;
;
ALTER TABLE "service_level" ADD CONSTRAINT "service_level_parent_id_name" UNIQUE ("parent_id", "name");
;
CREATE OR REPLACE FUNCTION "service_level_tree" (in integer)
RETURNS table( id int, parent_id int, name text, display text, depth int )
LANGUAGE sql
AS $$
WITH RECURSIVE service_level_tree (id, parent_id, name, display, depth ) AS (
SELECT
id,
parent_id,
name::text,
display,
1
FROM service_level
WHERE id = $1
UNION
SELECT
sl.id,
sl.parent_id,
sl.name,
sl.display,
depth +1
FROM service_level_tree t, service_level sl
WHERE sl.id = t.parent_id
AND depth < 10 -- Prohibit deep hierarchy
)
SELECT * FROM service_level_tree;/**/
$$
;
;
COMMIT;
is completed without errors. But function is not created.
If I add
select * from service_level_tree( 1 );
statement before commit then I get the error:
$(which dbic-migration) --schema_class HyperMouse::Schema --database PostgreSQL -Ilib upgrade
Reading configurations from /home/kes/work/projects/tucha/monkeyman/share/fixtures/138/conf
failed to run SQL in /home/kes/work/projects/tucha/monkeyman/share/migrations/PostgreSQL/upgrade/138-139/001-auto.sql: DBIx::Class::DeploymentHandler::DeployMethod::SQL::Translator::try {...} (): DBI Exception: DBD::Pg::db do failed: ERROR: function service_level_tree(integer) does not exist
LINE 1: select * from service_level_tree( 1 )
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts. at (eval 1094) line 17
(running line 'select * from service_level_tree( 1 )') at /home/kes/work/projects/tucha/monkeyman/local/lib/perl5/DBIx/Class/DeploymentHandler/DeployMethod/SQL/Translator.pm line 172.
DBIx::Class::Storage::TxnScopeGuard::DESTROY(): A DBIx::Class::Storage::TxnScopeGuard went out of scope without explicit commit or error. Rolling back. at /home/kes/work/projects/tucha/monkeyman/local/bin/dbic-migration line 0
DBIx::Class::Storage::TxnScopeGuard::DESTROY(): A DBIx::Class::Storage::TxnScopeGuard went out of scope without explicit commit or error. Rolling back. at /home/kes/work/projects/tucha/monkeyman/local/bin/dbic-migration line 0
Next upgrade script
is completed without errors. But function is not created.
If I add
statement before
commit
then I get the error: