为PL/SQL函数新增参数后，如何让原有变量引用该参数值？

Here's how to properly update your function to use the new parameter instead of the existing v_primary_photo variable:

1. Fix the Parameter Data Type #

Your initial parameter definition uses VARCHAR without a length, but the original v_primary_photo is VARCHAR(255). To maintain consistency and avoid type mismatches, update the parameter to match:

CREATE OR REPLACE FUNCTION community.publish_photo_story(
    p_story_id BIGINT, 
    p_author_id INT, 
    p_primary_photo VARCHAR(255) -- Match original variable's length
) RETURNS TABLE ( status VARCHAR(25), message VARCHAR(255) ) AS $func$

2. Remove the Unused Variable Declaration #

Since you'll be using the parameter directly instead of assigning to v_primary_photo, you can delete this line from the DECLARE section:

v_primary_photo VARCHAR(255); -- No longer needed

3. Adjust the Photo Fetch Query #

The original query was fetching both primary and thumbnail photos from core.photo. Now that you're passing the primary photo as a parameter, modify this query to only retrieve the thumbnail:

-- Before:
SELECT b_secured_url, t_secured_url INTO v_primary_photo, v_thumbnail_photo FROM core.photo WHERE photo_id = v_photo_id;

-- After:
SELECT t_secured_url INTO v_thumbnail_photo FROM core.photo WHERE photo_id = v_photo_id;

4. Replace All References to v_primary_photo with the Parameter #

Look for where v_primary_photo is used in the function—this is only in the UPDATE statement for community.story. Swap it out for p_primary_photo:

-- Before:
UPDATE community.story SET preview_content = v_caption, primary_photo = v_primary_photo, thumbnail_photo = v_thumbnail_photo, published_state = 'done', last_updated_date = now() WHERE story_id = p_story_id;

-- After:
UPDATE community.story SET preview_content = v_caption, primary_photo = p_primary_photo, thumbnail_photo = v_thumbnail_photo, published_state = 'done', last_updated_date = now() WHERE story_id = p_story_id;

Full Modified Function Code #

Here's the complete updated function with all changes applied:

CREATE OR REPLACE FUNCTION community.publish_photo_story(
    p_story_id BIGINT, 
    p_author_id INT, 
    p_primary_photo VARCHAR(255)
) RETURNS TABLE ( status VARCHAR(25), message VARCHAR(255) ) AS $func$ 
DECLARE 
    v_photo_id BIGINT; 
    v_caption TEXT; 
    v_thumbnail_photo VARCHAR(255); 
    v_tag_codes VARCHAR[]; 
    v_tag_ids INT[]; 
    v_status VARCHAR(25) := 'success'; 
    v_message VARCHAR(255) := 'Photo Story has been successfully published'; 
BEGIN 
    IF NOT EXISTS ( 
        SELECT 1 FROM community.story 
        WHERE story_id = p_story_id 
        AND published_state = 'in-progress' 
        AND is_deleted = false 
        AND author_id = p_author_id
    ) THEN 
        v_status := 'error'; 
        v_message := 'The story cannot be published'; 
        RETURN QUERY SELECT v_status, v_message; 
        RETURN; 
    END IF; 

    SELECT photo_id, caption INTO v_photo_id, v_caption FROM community.moment WHERE story_id = p_story_id ORDER BY display_order ASC LIMIT 1; 

    -- Only fetch thumbnail photo now
    SELECT t_secured_url INTO v_thumbnail_photo FROM core.photo WHERE photo_id = v_photo_id; 

    WITH tags AS ( 
        SELECT c.tag_id, c.tag_code FROM community.story_tag as ct 
        INNER JOIN community.tag as c ON ct.tag_id = c.tag_id 
        WHERE ct.story_id = p_story_id 
    ) SELECT array_agg(tag_id), array_agg(tag_code) INTO v_tag_ids, v_tag_codes FROM tags; 

    UPDATE community.post SET post_text = concat(v_caption), last_updated_date = now() WHERE story_id = p_story_id; 

    -- Substring caption to 160 chars for preview content 
    IF length(v_caption) > 160 THEN 
        v_caption := concat(substr(v_caption, 0, 160), '...'); 
    END IF; 

    -- Use the new parameter for primary_photo
    UPDATE community.story SET preview_content = v_caption, primary_photo = p_primary_photo, thumbnail_photo = v_thumbnail_photo, published_state = 'done', last_updated_date = now() WHERE story_id = p_story_id; 

    INSERT INTO audit.story_history( story_id, acted_by_id, log_message) VALUES( p_story_id, p_author_id, 'Photo story has been published'); 

    RETURN QUERY SELECT v_status, v_message; 
END; 
$func$ LANGUAGE plpgsql;

These changes ensure that all uses of the primary photo value come from your new parameter instead of the database query, while keeping the thumbnail photo logic intact.

内容的提问来源于stack exchange，提问作者Ram24