Project

General

Profile

« Previous | Next » 

Revision 10990

Added by Aaron Marcuse-Kubitza about 11 years ago

schemas/util.sql: mk_subset_by_row_num_func(): support tables whose pkeys don't start with 1 (such as the VegBank tables), by calculating the smallest row_num from the table

View differences:

schemas/util.sql
1384 1384 
CREATE FUNCTION mk_subset_by_row_num_func(view_ regclass, row_num_col text) RETURNS void
1385 1385 
    LANGUAGE plpgsql STRICT
1386 1386 
    AS $_$
1387 
DECLARE
1388 
	view_qual_name text = util.qual_name(view_);
1389 
	row_num__min__fn text = util.esc_name__append('__row_num__min', view_qual_name);
1387 1390 
BEGIN
1388 1391 
	EXECUTE $$
1392 
CREATE OR REPLACE FUNCTION $$||row_num__min__fn||$$()
1393 
  RETURNS integer AS
1394 
$BODY1$
1395 
SELECT $$||quote_ident(row_num_col)||$$
1396 
FROM $$||view_qual_name||$$
1397 
ORDER BY $$||quote_ident(row_num_col)||$$ ASC
1398 
LIMIT 1
1399 
$BODY1$
1400 
  LANGUAGE sql STABLE
1401 
  COST 100;
1402 
$$;
1403
1404 
	EXECUTE $$
1389 1405 
CREATE OR REPLACE FUNCTION $$||view_||$$(limit_ integer DEFAULT NULL::integer, offset_ integer DEFAULT NULL)
1390 1406 
  RETURNS SETOF $$||view_||$$ AS
1391 1407 
$BODY1$
1392 
SELECT * FROM $$||util.type_qual_name(view_::text::regtype)||$$
1393 
WHERE $$||quote_ident(row_num_col)||$$ BETWEEN util.offset2row_num($2) AND util.limit2row_num($1, $2)
1408 
SELECT * FROM $$||view_qual_name||$$
1409 
WHERE $$||quote_ident(row_num_col)||$$ BETWEEN
1410 
	util.offset2row_num(    $2, $$||row_num__min__fn||$$())
1411 
AND util.limit2row_num ($1, $2, $$||row_num__min__fn||$$())
1394 1412 
$BODY1$
1395 1413 
  LANGUAGE sql STABLE
1396 1414 
  COST 100
......
1403 1421 
  SET enable_sort TO 'off'
1404 1422 
  AS
1405 1423 
$BODY1$
1406 
SELECT * FROM $$||util.type_qual_name(view_::text::regtype)||$$($2, $3)
1424 
SELECT * FROM $$||view_qual_name||$$($2, $3)
1407 1425 
$BODY1$
1408 1426 
  LANGUAGE sql STABLE
1409 1427 
  COST 100

Also available in: Unified diff