Why can't I call a PL/pgSQL function from a pgAdmin pgScript?

Question

Our PostgreSQL database contains a function written in PL/pgSQL. I can run it just fine from an SQL query window like this:

BEGIN TRANSACTION;
SELECT sg_copy_form(414, 2621, 1, 1035);
ROLLBACK TRANSACTION;

However if I run exactly the same SQL above as a pgScript (for example, click the Execute pgScript button in pgAdmin instead of Execute query) then I get the following error:

[QUERY    ] BEGIN TRANSACTION
[WARNING  ] SELECT sg_copy_form(414, 2621, 1, 1035)
        ERROR:  function sg_copy_form(integer, integer, integer, integer) does not exist
        LINE 1: SELECT sg_copy_form(414, 2621, 1, 1035)
                       ^
        HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
[QUERY    ] ROLLBACK TRANSACTION

Why is this? Is it possible to call a PL/pgSQL function from a pgScript?

If relevant: I'm trying this on PostgreSQL 8.3.14 with pgAdmin III 1.12.2.

---

EDIT: I tried fully-qualifying the name of the function as suggested by Eelke, like this:

BEGIN TRANSACTION;
SELECT db_dev.public.sg_copy_form(414, 2621, 1, 1035);
ROLLBACK TRANSACTION;

It still works correctly when run as a normal SQL query, but now I get a different error message when I run it as a pgScript:

[QUERY    ] BEGIN TRANSACTION
[WARNING  ] SELECT db_dev.public.sg_copy_form(414, 2621, 1, 1035)
        ERROR:  cross-database references are not implemented: db_dev.public.sg_copy_form
[QUERY    ] ROLLBACK TRANSACTION

Answer 1

Did you double-check that you have the same environment in both cases? Please check

• same database,
• same user,
• same search_path

The problem is most probably that some settings in pgadmin3 are different and you just don't "see" the function.

Edit

You can compare the settings like this:

Execute this as plain SQL (i.e. only "Execute"):

select current_database(), current_user, current_setting('search_path');

Execute this as pgScript:

declare @foo{ @db, @usr, @p };
set @foo = select current_database(), current_user, current_setting('search_path');
print @foo;

End Edit

BTW: as the error message comes from the server, this would happen with functions written in any language (e.g. SQL, pljava), not just PL/pgSQL.