0

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
);

1 Answer 1

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.