Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.postgresql > #609

help with stored procedure

Show all headers | View raw

I have the following stored procedure.  
 def up
    execute <<-SPROC
      CREATE OR REPLACE function Adjusters_By_Team_Count (IN org_id int, IN start_timestamp timestamp, IN end_timestamp timestamp, IN approve_deny bigint, IN deny_approve bigint)

      RETURNS TABLE (
          user_id int,
          user_first_name varchar,
          user_last_name varchar,
          name varchar,
          action varchar,
          count bigint,
          decision varchar,
          denial_approval_count bigint,
          approval_denial_count bigint
         )
       AS $$

         SELECT users.id AS user_id, users.first_name AS user_first_name, users.last_name AS user_last_name,
         teams.name as team_name, action as action,count(dg.id) AS count,decision as decision, CAST(0 AS BIGINT),CAST(0 AS BIGINT)

    FROM determination_groups dg
         JOIN reviews rev  ON dg.review_id = rev.id
         JOIN users users  ON rev.user_id = users.id
         JOIN team_members ON users.id = team_members.user_id
         JOIN teams ON teams.id = team_members.team_id
       WHERE dg.action IS NOT NULL AND
        rev.organization_id = $1 AND
        rev.created_at >= $2 AND rev.created_at <= $3

    GROUP BY users.id,action,teams.name,decision
    ORDER BY users.id DESC



    RETURN;
    $$ LANGUAGE sql;
    SPROC
  end

I have two fields that need to be incremented based on value of two fields
so basically if action and decision = true then make denial_approval_count + 1.  I am thinking I would query the created table with some kind of cursor after the the order by function but I am new to psql.  Thanks any help would be great!

Back to comp.databases.postgresql | Previous | Next | Find similar

Thread

help with stored procedure David Gerber <gerbdla@gmail.com> - 2014-11-17 10:29 -0800

csiph-web