1

I am attempting to RETURN an integer from a plpgsql MERGE-related action that uses WITH. The value I want to RETURN is within the WITH statement below - the id. The error I receive with the below code is as follows:

relation "post_insert" does not exist"

Explaining the code: This code might not be the most efficient way to do the below, but I am setting things up to work for some future use cases where I think I will need to use other features of MERGE. All this does is create a post through Post_Insert, and then using that Post_Insert (which returns the post's id), I create rows in the Hashtags_Posts table that relate back to a master hashtags table.

The "RETURN (SELECT id FROM Post_Insert)" is what is causing the problem. I cannot seem to get the id from Post_Insert and return it.

Any help on how to access the id in Post_Insert would be greatly appreciated.

  create or replace function createPost(
  uid_query text, 
  post_text_query text default '', 
  text_url_query text default '', 
  photo_url_query text default '', 
  video_url_query text default '', 
  audio_url_query text default '',
  location_query text default '',
  tags_array_query text[] default '{general}',  
  photo_query boolean default false
  )
returns integer language plpgsql as $$

BEGIN

WITH 
Post_Insert AS (
  INSERT INTO "Posts" (
    author, 
    post_text, 
    text_url, 
    photo_url, 
    video_url, 
    audio_url, 
    location,
    photo
  )
  VALUES (
    uid_query, 
    post_text_query, 
    text_url_query, 
    photo_url_query, 
    video_url_query, 
    audio_url_query,
    location_query,
    photo_query
  )
  RETURNING id
)

MERGE INTO "Hashtags_Posts" HP 
USING (SELECT * 
        FROM "Hashtags" H
        WHERE  H.hashtag = ANY(tags_array_query))
        as TMT
ON HP.id = (SELECT id FROM Post_Insert) AND TMT.id = HP.hashtag_id
WHEN NOT MATCHED
  THEN  INSERT (post_id, hashtag_id)
        VALUES ((SELECT id FROM Post_Insert), TMT.id);

RETURN (SELECT id FROM Post_Insert);
END 
$$
0

1 Answer 1

1

I don't see any reason why INSERT and MERGE would have to be in the same statement. (The whole function runs within the same transaction in any case.) Just split them up, and save the resulting id in a PL/pgSQL variable like instructed in the manual here.

Or, better yet, use an OUT parameter instead of a variable to simplify things:

CREATE OR REPLACE FUNCTION create_post(
     uid_query         text
   , post_text_query   text default '' 
   , text_url_query    text default '' 
   , photo_url_query   text default '' 
   , video_url_query   text default '' 
   , audio_url_query   text default ''
   , location_query    text default ''
   , tags_array_query  text[] default '{general}'  
   , photo_query       bool default false
   , OUT post_insert_id int                   -- use your actual data type!
   )
  RETURNS integer
  LANGUAGE plpgsql AS
$func$
BEGIN
   INSERT INTO "Posts"
          (author   , post_text      , text_url      , photo_url      , video_url      , audio_url      , location      , photo)
   VALUES (uid_query, post_text_query, text_url_query, photo_url_query, video_url_query, audio_url_query, location_query, photo_query)
   RETURNING id
   INTO post_insert_id;

   MERGE INTO "Hashtags_Posts" hp
   USING (SELECT * 
          FROM   "Hashtags" h
          WHERE  h.hashtag = ANY(tags_array_query)) tmt
             ON hp.id = post_insert_id
            AND tmt.id = hp.hashtag_id
   WHEN NOT MATCHED THEN
      INSERT (post_id, hashtag_id)
      VALUES (post_insert_id, tmt.id);

   -- no separate RETURN needed, post_insert_id is declared OUT param
END 
$func$;

And my standing advice is to use legal, lower-case identifiers in Postgres, which can't be messed up by (missing) double-quotes. See:

0

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.