PostgreSQL conditional use of function parameters

Question

I'd like to know how to use a function parameter conditionaly. This is my function, and you can read the comment inside the query:

CREATE OR REPLACE FUNCTION mediabase.select_media(sysEnvironment character varying, statusId integer)
RETURNS refcursor AS
$BODY$
DECLARE
  ref refcursor; 
BEGIN
  OPEN ref FOR 

  SELECT media.id, media.title, media.unique_filename, media.owner_id, media.status_id, media.location_name_id, media.upload_user_id, media.upload_ip, media.metadata_id, media.type_id, media.description, media.system_environment, media.upload_date, media.gps_location, media.language_id, media_publications.publication_id, media.limitations, media_categories.category_id, metadata.width, metadata.height, metadata.equipment, metadata.copyright, metadata.creation_time, metadata.file_format, metadata.resolution, metadata.resolution_unit, metadata.gps_longitude, metadata.gps_latitude, metadata.artist, metadata.color_space, metadata.gps_altitude, metadata.software_used, metadata.user_comment
  FROM mediabase.media, mediabase.metadata, mediabase.media_categories, mediabase.media_publications
  WHERE media.metadata_id = metadata.id
  AND media.id = media_categories.media_id
  AND media.id = media_publications.media_id
  -- Problem: this CASE doesn't work of course
  CASE statusId <> -1 THEN
    AND media.status_Id = statusId
  END
  -- End problem
  AND media.system_environment = sysEnvironment
  ORDER BY media.upload_date DESC;

  RETURN ref;                       
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

I only need to use the 'statusId' parameter, if it's different from -1, otherwise I'll recieve no results as there of-course is no status -1. Later on, I'll need to add some more filters of that sort.

Ihor Romanchenko · Accepted Answer · 2013-01-15 12:29:05Z

1

Try something like:

AND (media.status_Id = statusId OR statusId = -1)

It wont check media.status_Id = statusId if statusId = -1.

answered Jan 15, 2013 at 12:29

Ihor Romanchenko

29k9 gold badges56 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Clodoaldo Neto Over a year ago

Although the solution is correct and simpler than a case there is nothing to prevent it from checking the left side.

Ihor Romanchenko Over a year ago

@Clodoaldo Haven't tested on a full procedure, but this example shows, that query optimizer can omit useless checks.

Collectives™ on Stack Overflow

PostgreSQL conditional use of function parameters

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related