PL/pgSQL anonymous code block
There must be an oid constant in ALTER LARGE OBJECT oid ...
. Try this workaround:
DO $$
DECLARE
bigobject integer;
BEGIN
SELECT lo_creat(-1) INTO bigobject;
EXECUTE 'ALTER LARGE OBJECT ' || bigobject::text || ' OWNER TO postgres';
...
The same also applies to GRANT and REVOKE, of course.
In addition to what @klin already cleared up, you cannot use SELECT
without a target in plpgsql code. Replace it with PERFORM
in those calls.
Aside: Using "com.ektyn.eshops.myuser"
as name for a role is a terrible idea. Use legal, lower case identifiers that don't have to be double-quoted.
This is an artifact of the fact that PostgreSQL has two completely different kinds of SQL statements internally - plannable (SELECT
, INSERT
, UPDATE
, and DELETE
) and unplannable (everything else) statements.
Only plannable statements support query parameters.
PL/pgSQL implements variable substitutions into statements, like your bigobject
, using query parameters.
Because they aren't supported for non-plannable statements, no substitution is performed. So PL/pgSQL tries to execute the statement literally, as if you'd typed:
ALTER LARGE OBJECT bigobject OWNER TO postgres;
directly at the psql
prompt. It does not detect this as an error.
To work around this, use EXECUTE ... FORMAT
, e.g.
EXECUTE format('ALTER LARGE OBJECT %s OWNER TO postgres', bigobject);
See this related answer about COPY
.