PostgreSQL function call error: structure of query does not match function result type



  • i have function

    CREATE FUNCTION create_post(
        txt text,
        created BIGINT,
        photourl text,
        user_id bigint
    ) RETURNS TABLE(
        id bigint,
        "text" text,
        created_at timestamp,
        photo_url text,
        user_email text,
        comments_count int
    ) AS $$
    DECLARE ret_id bigint;
    

    BEGIN
    INSERT INTO posts(
    "text",
    created_at,
    photo_url,
    user_id
    )
    VALUES ($1, to_timestamp($2), $3, $4)
    RETURNING posts.id INTO ret_id;

    RETURN query
    SELECT p.id AS id,
    p.text AS "text",
    p.created_at AS created_at,
    p.photo_url AS photo_url,
    u.email AS user_email,
    COUNT(c) AS comments_count
    FROM posts AS p
    INNER JOIN users AS u ON u.id = p.user_id
    LEFT JOIN comments AS c ON c.post_id = p.id
    WHERE p.id = ret_id
    GROUP BY p.id,
    u.email;

    END $$ LANGUAGE plpgsql;

    It created successfully, but when call function SELECT create_post('haha', 1652131509, 'photo', 2); get error structure of query does not match function result type. Used tables:

    CREATE TABLE "users" (
        "id" BIGSERIAL PRIMARY KEY,
        "email" text UNIQUE NOT NULL,
        "encrypted_password" text NOT NULL,
        "created_at" timestamp NOT NULL
    );
    

    CREATE TABLE "posts" (
    "id" BIGSERIAL PRIMARY KEY,
    "text" text,
    "created_at" timestamp NOT NULL,
    "photo_url" text,
    "user_id" bigint NOT NULL,
    FOREIGN KEY ("user_id") REFERENCES "users" ("id") ON DELETE CASCADE
    );



  • count(*) returns BIGINT in with plpgsql, NOT JUST INT




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2