cant call function due to strange type error
Open
Nobody has claimed this yet.
- Dominant language
- JavaScript
- Stars
- 8.7k
- Forks
- 374
- Avg merge
- 11d 16h
- Merged PRs (30d)
- 1
Description
for some reason the driver casts a number to a string?
schema:
CREATE EXTENSION pgcrypto;
CREATE TABLE users (
id SERIAL NOT NULL PRIMARY KEY,
username TEXT NOT NULL UNIQUE,
password TEXT NOT NULL
);
CREATE TABLE sessions (
id uuid NOT NULL DEFAULT GEN_RANDOM_UUID() PRIMARY KEY,
expires_at timestamp NOT NULL DEFAULT NOW() + INTERVAL '1 day',
user_id int NOT NULL REFERENCES users(id) ON DELETE CASCADE
);
CREATE OR REPLACE FUNCTION update_password(
p_userid INT,
p_new_password TEXT
) RETURNS TABLE(session_id uuid, expires_at timestamp) AS $$
DECLARE
v_user_id INT;
BEGIN
UPDATE users
SET password = crypt(p_new_password, gen_salt('bf'))
WHERE id = p_userid
RETURNING id INTO v_user_id;
IF NOT FOUND THEN
RAISE EXCEPTION 'User not found';
END IF;
DELETE FROM sessions WHERE user_id = v_user_id;
INSERT INTO sessions (user_id)
VALUES (v_user_id)
RETURNING id, sessions.expires_at INTO session_id, expires_at;
RETURN NEXT;
END;
$$ LANGUAGE plpgsql;
query:
await sql`SELECT * FROM update_password(${38},${"new password"})`
error:
Uncaught PostgresError: operator does not exist: integer = text
at ErrorResponse (file:///home/angst/dev/links/node_modules/postgres/src/connection.js:788:26)
at handle (file:///home/angst/dev/links/node_modules/postgres/src/connection.js:474:6)
at Socket.data (file:///home/angst/dev/links/node_modules/postgres/src/connection.js:315:9)
at Socket.emit (node:events:518:28)
at Socket.emit (node:domain:552:15)
at addChunk (node:internal/streams/readable:561:12)
at readableAddChunkPushByteMode (node:internal/streams/readable:512:3)
at Readable.push (node:internal/streams/readable:392:5)
at TCP.onStreamRead (node:internal/stream_base_commons:191:23)
at TCP.callbackTrampoline (node:internal/async_hooks:130:17)
at cachedError (file:///home/angst/dev/links/node_modules/postgres/src/query.js:170:23)
at new Query (file:///home/angst/dev/links/node_modules/postgres/src/query.js:36:24)
at sql (file:///home/angst/dev/links/node_modules/postgres/src/index.js:112:11)
at REPL25:1:40 {
severity_local: 'ERROR',
severity: 'ERROR',
code: '42883',
hint: 'No operator matches the given name and argument types. You might need to add explicit type casts.',
where: 'PL/pgSQL function update_password(text,text) line 6 at SQL statement',
internal_position: '84',
internal_query: 'UPDATE users\n' +
" SET password = crypt(p_new_password, gen_salt('bf'))\n" +
' WHERE id = p_userid\n' +
' RETURNING id',
file: 'parse_oper.c',
line: '635',
routine: 'op_error'
}
if i run same query via psql it works fine..
SELECT * FROM update_password(38,'newpass');
Contributor guide
No contributing guide indexed for this repository
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
Research direction
Reproduce the JavaScript template query against the supplied PostgreSQL function and compare it with the psql call. Start by tracing the parameter and query handling around node_modules/postgres/src/query.js and connection.js; done means the JavaScript call resolves the function with the intended argument types without the integer-versus-text operator error.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- javascript, nodejs, postgresql
- Domain
- backend, database
- Issue type
- Bug
- Difficulty
- 3/5
- Estimated time
- 1-2 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 35/100