happysundar
6/14/2014 - 8:06 PM

Creating a plpgsql function

Creating a plpgsql function

DROP FUNCTION IF EXISTS get_image_records( BIGINT ) CASCADE;
CREATE OR REPLACE FUNCTION
  get_image_records(input_program_id BIGINT)
  RETURNS SETOF JSON STABLE
AS $$
BEGIN
  RETURN QUERY
  WITH T2 AS (
      SELECT
        file_url :: TEXT,
        file_height,
        file_width,
        file_size,
        number_of_people,
        zoom_level,
        caption :: TEXT,
        primary_image,
        image_type_name :: TEXT
      FROM export.programimages_imagefiles
      WHERE programimages_imagefiles.program_id = input_program_id)
  SELECT
    json_agg(to_json(T2.*))
  FROM T2;
END
$$ LANGUAGE plpgsql;