Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE OR REPLACE FUNCTION readMessage(messageFor INT, qid INT = NULL, ctxt INT = NULL, messageFrom INT = NULL, byTimeStamp BOOLEAN = FALSE)
- RETURNS SETOF message
- AS $$
- DECLARE
- sql varchar;
- BEGIN
- sql := 'SELECT * FROM message WHERE (receiver_id IS NULL OR receiver_id = $1)';
- IF qid IS NOT NULL THEN
- sql := sql || ' AND queue_id = $2';
- END IF;
- IF ctxt IS NOT NULL THEN
- sql := sql || ' AND context = $3';
- ELSE
- sql := sql || ' AND context IS NULL';
- END IF;
- IF $4 IS NOT NULL THEN
- sql := sql || ' AND sender_id = $4';
- END IF;
- sql := sql || ' ORDER BY';
- IF byTimeStamp THEN
- sql := sql || ' arrive_time ASC';
- ELSE
- sql := sql || ' priority DESC';
- END IF;
- sql := sql || ' LIMIT 1';
- RETURN QUERY EXECUTE sql
- USING messageFor, qid, ctxt, messageFrom;
- END;
- $$
- LANGUAGE plpgsql;
- Connection con;
- PreparedStatement ps = con.prepareStatement("select readMessage(1, 1, 1, 1, true))";
- ResultSet rs = ps.executeQuery();
- rs.getInt(<column-name>)
- rs.getInt(<column-index>)
- CREATE TABLE test.child (
- id integer,
- p_id1 integer,
- p_id2 integer
- );
- CREATE OR REPLACE FUNCTION named_cols()
- RETURNS SETOF test.child
- LANGUAGE sql
- AS $$ SELECT 0, 12, 23; $$;
- SELECT named_cols();
- named_cols
- ────────────
- (0,12,23)
- SELECT * FROM named_cols();
- id │ p_id1 │ p_id2
- ────┼───────┼───────
- 0 │ 12 │ 23
- SELECT * FROM [FUNCTION_NAME()]
Add Comment
Please, Sign In to add comment