Postgres Pro
Downloads
Home   >   mailing lists

Thread: Help with PLPGSQL syntax please

Help with PLPGSQL syntax please

From
"Edward Grabczewski"
Date:
Could someone please tell me the correct syntax for the following problem in
plpgsql:

The following expression code sample works but now (for example) I'd like to
subsitute
the variables $1 and $2  for the numbers 0 and 2 in the following
expression:

INSERT INTO rtest(xz,yz,xy) VALUES ( \'(0,2), (1,3)\',
                                                                 \'(1,2),
(2,3)\',
                                                                 \'(0,1),
(1,2)\');

What's the correct way of represinting the strings?

A complete code example is below. It's to do with the usage of R-tree
indexing.

Eddy
edward.grabczewski@btinternet.com
===============================================================
DROP TABLE rtest;
DROP INDEX rtest_xz_index;
DROP INDEX rtest_yz_index;
DROP INDEX rtest_xy_index;

CREATE TABLE rtest (
                                     xz BOX,
                                     yz BOX,
                                     xy BOX);

CREATE INDEX rtest_xz_index ON rtest USING RTREE (xz bigbox_ops);
CREATE INDEX rtest_yz_index ON rtest USING RTREE (yz bigbox_ops);
CREATE INDEX rtest_xy_index ON rtest USING RTREE (xy bigbox_ops);

DROP FUNCTION threedpoint (float,float,float, float,float,float);
CREATE FUNCTION threedpoint (float,float,float,float,float,float)
RETURNS text
AS 'DECLARE
      x1 ALIAS FOR $1;
      y1 ALIAS FOR $2;
      z1 ALIAS FOR $3;
      x2 ALIAS FOR $4;
      y2 ALIAS FOR $5;
      z2 ALIAS FOR $6;
    BEGIN
      INSERT INTO rtest(xz,yz,xy)
      VALUES ( \'(0,2), (1,3)\',
                        \'(1,2), (2,3)\',
                        \'(0,1), (1,2)\');
      RETURN null;
    END;'
LANGUAGE 'plpgsql';

SELECT threedpoint(100,200,300,400,500,600);

SELECT * FROM rtest
ORDER BY xz USING <<;

SELECT xz, yz, xy FROM rtest
WHERE xz @ '(1.0,3.0),(0.0,2.0)'::box
AND   yz @ '(2.0,3.0),(1.0,2.0)'::box
AND   xy @ '(1.0,2.0),(0.0,1.0)'::box
ORDER BY xz USING <<;

========================================================

Re: Help with PLPGSQL syntax please

From
"Edward Grabczewski"
Date:
Could someone please tell me the correct syntax for the following problem in
plpgsql:

The following expression code sample works but now (for example) I'd like to
subsitute
the variables $1 and $2  for the numbers 0 and 2 in the following
expression:

INSERT INTO rtest(xz,yz,xy)
VALUES ( \'(0,2), (1,3)\',
                  \'(1,2), (2,3)\',
                  \'(0,1), (1,2)\');

What's the correct way of represinting the strings?

A complete code example is below. It's to do with the usage of R-tree
indexing.

Eddy
edward.grabczewski@btinternet.com
===============================================================
DROP TABLE rtest;
DROP INDEX rtest_xz_index;
DROP INDEX rtest_yz_index;
DROP INDEX rtest_xy_index;

CREATE TABLE rtest (
                                     xz BOX,
                                     yz BOX,
                                     xy BOX);

CREATE INDEX rtest_xz_index ON rtest USING RTREE (xz bigbox_ops);
CREATE INDEX rtest_yz_index ON rtest USING RTREE (yz bigbox_ops);
CREATE INDEX rtest_xy_index ON rtest USING RTREE (xy bigbox_ops);

DROP FUNCTION threedpoint (float,float,float, float,float,float);
CREATE FUNCTION threedpoint (float,float,float,float,float,float)
RETURNS text
AS 'DECLARE
      x1 ALIAS FOR $1;
      y1 ALIAS FOR $2;
      z1 ALIAS FOR $3;
      x2 ALIAS FOR $4;
      y2 ALIAS FOR $5;
      z2 ALIAS FOR $6;
    BEGIN
      INSERT INTO rtest(xz,yz,xy)
      VALUES ( \'(0,2), (1,3)\',
                        \'(1,2), (2,3)\',
                        \'(0,1), (1,2)\');
      RETURN null;
    END;'
LANGUAGE 'plpgsql';

SELECT threedpoint(100,200,300,400,500,600);

SELECT * FROM rtest
ORDER BY xz USING <<;

SELECT xz, yz, xy FROM rtest
WHERE xz @ '(1.0,3.0),(0.0,2.0)'::box
AND   yz @ '(2.0,3.0),(1.0,2.0)'::box
AND   xy @ '(1.0,2.0),(0.0,1.0)'::box
ORDER BY xz USING <<;

========================================================