Comments (11)
What do you expect from this example? The current intention is that
plv8.execute() doesn't take "type" arguments so it infers the PG type from JS
type of the input values. The intended way to work with your example is,
plv8.execute("select * from typetest where id = $1", [1])
to tell plv8 that the SQL parameter $1 is an integer by giving 1 not '1'.
Original comment by [email protected]
on 21 May 2012 at 5:35
from plv8.
The example was to show that pg seems to be fine with coercing types, in that
select * from typetest where id = '1'
works just as select * from typetest where id = 1
I understand your point about the entended use of the API, but I believe this
to be a common use case when passing JSON as a parameter into a plv8 function.
I haven't gotten to dates yet, but I'm assuming if an string representation of
an integer will error, then a date string of '2012-05-16 15:24:51.195555-07'
will not work without first calling new Date('2012-05-16 15:24:51.195555-07').
Postgres also has many types not available in js, 64 bit integers would need to
be passed in as strings.
I think I remember having an issue with plv8 when using enums as well (getting
an error when passing the enum value as a string).
Sorry for not being more concrete and test this out some more, but I'm right in
the middle of hacking/testing some stuff with 9.2 and I haven't put plv8 back
in yet and I'm loosing a battle with the clock to get the stakeholder to buy in
on pg and js over some other alternatives.
Original comment by [email protected]
on 21 May 2012 at 1:35
from plv8.
Hmm, I see. Let me take a closer look at it.
Original comment by [email protected]
on 22 May 2012 at 5:04
from plv8.
In your example, the constant '1' is of type "unknown". I think the this kind
of problems have been there around like JDBC, and are not easy to solve (one
way has pros and cons). I believe plv8.execute should follow those external
driver behavior to avoid surprises (you have a surprise here, though :)
If I recall correctly, in cases like JDBC, your repro should look like
select * from typetest where id = $1::int
with int cast for $1.
Original comment by [email protected]
on 29 May 2012 at 6:16
from plv8.
I don't have any experience with JDBC, but node-postgres (the pg driver for
node) handles the type coercion. My goal is to port an app that currently uses
node-postgres for all queries to plv8 and am writing a type of RESTful db
access layer in plv8.
I ran the below queries with node-postgres:
pg.connect("tcp://postgress@localhost/testdb",function(err,client){
client.query("select * from typetest where id = $1",['1'],function(err,result){
console.log('STR::INT:',err,result);
});
client.query("select * from typetest where id = $1",['NAN'],function(err,result){
console.log('NAN:',err,result);
});
});
OUTPUT:
STR::INT: null { rows: [ { id: 1, name: 'test' } ],
command: 'SELECT',
rowCount: 1,
oid: NaN }
NAN: { [error: invalid input syntax for integer: "NAN"]
length: 85,
name: 'error',
severity: 'ERROR',
code: '22P02',
detail: undefined,
hint: undefined,
position: undefined,
internalPosition: undefined,
internalQuery: undefined,
where: undefined,
file: 'numutils.c',
line: '62',
routine: 'pg_atoi' }
What is interesting about this output is node-postgres knows it is binding to
an int, without help from my code, and if I pass a string that can not be
casted to an integer I get an error out of pg_atoi. But I think node-postgres
is just handling off to the postgres library and getting type coercion built in.
The plv8 error is "Error: operator does not exist: integer = text" so it
appears that somebody knows the underlying type is an integer and the passed
value is text and somwhere postgres knows to call pg_atoi on the value instead
of throwing back an error.
We are probably going to work around this issue for now by querying pg_catalog
and getting the type information and decorate each $x with the proper ::type,
but that's probably going to slow it down a bit.
Original comment by [email protected]
on 29 May 2012 at 5:38
from plv8.
Just ran into the same issue with citext, only instead of an error I just get
an empty result.
I have a users table where the id column is of type citext:
plv8.execute("SELECT * from users where id=$1",['krazykrut']) -- RETURNS []
plv8.execute("SELECT * from users where id=$1",['KrazyKrut']) -- RETURNS
[{id:"KrazyKrut", ...}]
plv8.execute("SELECT * from users where id=$1::citext",['krazykrut']) --
RETURNS [{id:"KrazyKrut", ...}]
So it seems that plv8 will not use the comparative properties of the underlying
type unless explicitly told to do so.
Original comment by [email protected]
on 30 May 2012 at 12:40
from plv8.
OK, I think I understand the difference between plv8 and node-postgres.
This is not only node-postgres, but client applications are allowed to omit
parameter types when the statement is prepared. In that case, the backend
parser deduces each type of parameters and your example is exactly this case.
If the client program including node-postgres gives explicit type information,
the situation is same as plv8.
The problem is, however, this is not easy for plv8 which uses SPI instead of
libpq. All the SPI interfaces seem to prohibit NULL input to argtypes. If
given, SPI immediately returns with an error code. An exception might be
SPI_prepare_params, which lets the caller of SPI to hook the parameter
resolution. I need to try and see if it works as expected, but at least this
interface was introduced in 9.0. Personally, I'd like to make the behavior
same since 8.4 to the latest version. Now we're going to get 9.2, we could
drop 8.4, though...
Original comment by [email protected]
on 30 May 2012 at 5:49
from plv8.
I've just created a topic branch for this. Can you update your git repository
and checkout 'deduce_paramtype' branch? It requires 9.1. If the behavior is
what you want, I'll clean up and wrap it up.
Original comment by [email protected]
on 31 May 2012 at 7:22
- Changed state: Accepted
from plv8.
Thanks, works great. Tested with int, timestamp, enum, text and citext!
Original comment by [email protected]
on 31 May 2012 at 4:40
from plv8.
Thanks for testing. I'm thinking of the design aspect, as it's better to align
the behavior with plv8.prepare() and cursors. Soon I'll release v1.1.0 to
PGXN, and after that I'll incorporate this along with the session reset issue,
as these are kind of new features.
Original comment by [email protected]
on 1 Jun 2012 at 6:12
from plv8.
Now it's on master branch. Prepared plans also make use of it.
http://code.google.com/p/plv8js/source/detail?r=9904bc94cb9450d7e746d1f76a1ded96
fa060145
Original comment by [email protected]
on 26 Jun 2012 at 7:01
- Changed state: Fixed
from plv8.
Related Issues (20)
- Compatibility with Postgres 16 HOT 2
- Build error plv8 - using the `make` command HOT 8
- Upgrade from 2.3.12 to 2.3.15 failed in Postgresql v11.18 HOT 1
- Excited to Start working with 3.2alpha Branch HOT 2
- Invalid Discord link? HOT 2
- r3.2 Rocks HOT 2
- Many warnings when building v3.2.0 HOT 2
- /usr/bin/ld: cannot find -lv8_base_with_compiler: No such file or directory HOT 4
- Support for pg16 HOT 4
- plv8_info() causes segmentation fault HOT 2
- Unable to access transition tables declared in trigger HOT 1
- add functions setTimeout and setInterval HOT 11
- Async function call in trigger HOT 4
- Use of Promise HOT 2
- PGXN Build Fails
- undefined symbol: _ZTIN2v84base6ThreadE HOT 11
- Getting Error '-fexcess-precision=standard' is not supported when trying to compile PLV8 with EDB16 HOT 1
- Can't get access to things defined using start_proc HOT 9
- Build guide mistakes HOT 4
- How to use OUT params in plv8 stored proc HOT 1
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from plv8.