SQLExceptionHelper Invalid Input syntax for integer (PL/pgSQL)

Question

I want to get an integer from a database query:

SELECT CAST(concat('id_',substr_of_jointable) AS INTEGER) into integervalue 
  FROM books_authors where id_books = booksvalue 
  ORDER BY id_books DESC 
  LIMIT 1;

subsr_of_jointable is a TEXT with value authors. However I always get an error:

ERROR org.hibernate.engine.jdbc.spi.SqlExceptionHelper - ERROR: invalid input syntax for integer: "id_authors"
Wobei: SQL statement "SELECT CAST(concat('id_',substr_of_jointable)AS INTEGER) FROM books_authors where id_books = books_value ORDER BY id_books DESC LIMIT 1"
PL/pgSQL function books_ins_trig_proc() line 125 at SQL statement

Does anyone have an idea why? The column id_books id_authors is an integer value in the database.

You are trying to convert a string, 'id_authors' to a number - that won't work — user330315
– user330315, Commented Feb 4, 2020 at 8:59

Jim Jones · Accepted Answer · 2020-02-04 09:46:24Z

1

Assuming you're trying to build a dynamic query inside a PL/pgSQL function, you might want to take a look at this approach.

Data sample

CREATE TABLE t (id_authors INT);
INSERT INTO t VALUES (1);

Function

CREATE OR REPLACE FUNCTION myfunction(TEXT) RETURNS INT 
LANGUAGE 'plpgsql' AS $BODY$
DECLARE i INT;
BEGIN
  EXECUTE 'SELECT id_'|| $1 ||' FROM t LIMIT 1;' INTO i;
  RETURN i;
END;
$BODY$;

This example is only to shows how you can concatenate your strings to create a column name inside your dynamic query.

Calling the function

SELECT * FROM myfunction('authors');

 myfunction 
------------
          1
(1 Zeile)

3 Comments

Dennis Over a year ago

Whats that $1 in your EXECUTE? Also is this thee execute sql function? I googled it minutes before :D

Jim Jones Over a year ago

@Dennis $1 means the first parameter of your function, which in this example contains the value authors. If you prefer, you can give it a label in the function signature, e.g. CREATE OR REPLACE FUNCTION myfunction(param TEXT)... and your EXECUTE would look like this: EXECUTE 'SELECT id_'|| param ||' FROM t LIMIT 1;' INTO i;

Jim Jones Over a year ago

EXECUTE, as the name suggests, executes a query based on a string. This makes possible, for instance, to concatenate column and table names, which isn't possible in pure SQL.

Collectives™ on Stack Overflow

SQLExceptionHelper Invalid Input syntax for integer (PL/pgSQL)

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related