Passing a variable in postgres typecast

Question

I am trying to do the following in Postgresql

SELECT * FROM table_one
INNER JOIN table_two ON table_one.id = table_two.table_one_id
WHERE (table_one.some_value > length('((-1,0),(table_two.x,table_two.y))'::lseg))

Obviously this doesn't work so my question is how do you do something like this?

'((-1,0),(%,%))'::lseg , table_two.x , table_two.y

or

'((-1,0),('|| table_two.x ||',('|| table_one.x || '))'::lseg

The second one should work… What happened when you tried it? — Denis de Bernardy
– Denis de Bernardy, Commented Nov 26, 2013 at 19:32
I am getting this ERROR: invalid input syntax for type lseg also the x and y columns are floats — marknery
– marknery, Commented Nov 26, 2013 at 19:45

Bell · Accepted Answer · 2013-11-26 19:43:04Z

1

You could make your second suggestion work by bracketing it more clearly - casting takes precedence to concatenation so it is trying to cast '))' to an lseg. If you bracket the concatenation before casting it the statement will work ...

length(('((-1,0),('|| table_two.x ||','|| table_two.y || '))')::lseg)

But it would be neater to use the fields values in type conversion functions. At least that way the error message would make more sense if there were any.

In this case define the lseg with two points. Build one of the points is built from a literal and one from the fields in a table.

length(lseg('(-1,0)'::point, point(table_two.x, table_two.y)))

Or both from numeric values, which I think looks best and also happens to be shortest

length(lseg(point(-1,0), point(table_two.x, table_two.y)))

answered Nov 26, 2013 at 19:43

Bell

19k4 gold badges26 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Passing a variable in postgres typecast

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related