porsager / porsager/postgres

cant call function due to strange type error

Open
#1,026 1 comment 0 reactions 0 assignees View on GitHub

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

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.