PL/pgSQL syntax for strings

Поиск
Список
Период
Сортировка
От Edward Grabczewski
Тема PL/pgSQL syntax for strings
Дата
Msg-id 9rru48$8gh$1@news.tht.net
обсуждение исходный текст
Ответы Re: PL/pgSQL syntax for strings  (Roberto Mello <rmello@cc.usu.edu>)
Список pgsql-sql
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 <<;

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






--
Eddy Grabczewski
edward.grabczewski@btinternet.com





В списке pgsql-sql по дате отправления:

Предыдущее
От: "James Orr"
Дата:
Сообщение: Re: transposing data for a view
Следующее
От: "James Orr"
Дата:
Сообщение: Aggregate binary AND