CODEWITHSUNDEEP
javapostgresqlhibernate
Zmur
Zmur

Reputation: 334

PSQLException: A result was returned when none was expected

When I try to run SQL scripts which have functions in them, the system throws an error: org.postgresql.util.PSQLException: A result was returned when none was expected..

The task is to take bunch of sql files and execute them on DB. I use hibernate and postgres, here are the dependencies from POM:

    <dependency>
        <groupId>org.hibernate</groupId>
        <artifactId>hibernate-java8</artifactId>
        <version>5.0.12.Final</version>
    </dependency>
    <dependency>
        <groupId>org.postgresql</groupId>
        <artifactId>postgresql</artifactId>
        <version>42.2.5</version>
    </dependency>

All the files are executed one after another in a loop. I begin transaction before starting the loop and commit it after the loop has ended. Text from each file is passed into Query sqlQuery = session.createSQLQuery(script) and after that I call sqlQuery.executeUpdate(). This is where it fails when encounters a script with function. It seems that only more complex functions fail, small functions pass OK

This, for example passes:

DO'
DECLARE property RECORD;
BEGIN
  FOR property IN SELECT id, default_value
    FROM schema.table WHERE type_id = 3 LOOP
      if (property.default_value) IS NULL THEN
        UPDATE schema.table SET default_value = ''DEFAULT'' WHERE id = property.id;
      END IF ;
  END LOOP;
END';

This fails ('schema.' means some schema, such as 'public'):

CREATE OR REPLACE FUNCTION theScript(TEXT)
  RETURNS VOID AS '
DECLARE
  _table_name ALIAS FOR $1;
  _sub_id integer;
  _prop_id integer;
  _ent_id integer;
  _ent_sub_id integer;
BEGIN
  FOR _ent_id IN EXECUTE ''SELECT id FROM schema.'' || _table_name LOOP
    EXECUTE '' SELECT sub_id FROM schema.'' || _table_name || '' WHERE id = '' || _ent_id
    INTO _ent_sub_id;
    IF _ent_sub_id IS NULL THEN
      EXECUTE '' SELECT sub_property_id FROM schema.'' || _table_name || ''_data WHERE '' || _table_name || ''_id = '' || _ent_id || '' LIMIT 1''
      INTO _prop_id;
      SELECT subdivision_id
      from bms.subdivision_property
      where id = _prop_id
      INTO _sub_id;
      EXECUTE '' UPDATE schema.'' || _table_name || '' SET sub_id = '' || (_sub_id) || '' WHERE id = '' || _ent_id;
      RAISE NOTICE ''UPDATED FIELD ID: %'', _ent_id;
    END IF;
  END LOOP;
END;'
LANGUAGE plpgsql;

SELECT theScript('terminal');

What is the possible cause of this?

Upvotes: 2

Views: 6789

Answers (1)

Emilio Platzer
Emilio Platzer

Reputation: 2469

select void

In your SELECT statement, the SELECT returns one row (because no FROM and no WHERE) with one column of type void. It returns something. 

create function is_void(p_text text) returns void
  language plpgsql as
'begin
end';

select is_void('one');

If you want to return nothing yo can do something like this:

create function is_void(p_text text) returns void
  language plpgsql as
'begin
end';

do $$
begin
  perform is_void('one');
end
$$;

Upvotes: 3

Related Questions