[SQL] SQL FUNCTION return type on INSERT

[Edward Grabczewski]

What value should I RETURN for a SQL FUNCTION that contains an INSERT
statement?

--
Eddy Grabczewski
[EMAIL PROTECTED]




---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] SQL FUNCTION return type on INSERT

[Edward Grabczewski]

I've just looked at the new online 7.2 documentation and found that all
functions must end in some kind of select.
How about this then?

CREATE FUNCTION foo(varchar)
RETURN unknown
AS '
  INSERT INTO footable VALUES ($1);
  SELECT null; '
LANGUAGE 'sql';

"Edward Grabczewski" <[EMAIL PROTECTED]> wrote in message
9s0n0p$1saj$[EMAIL PROTECTED]">news:9s0n0p$1saj$[EMAIL PROTECTED]...
> What value should I RETURN for a SQL FUNCTION that contains an INSERT
> statement?
>
> --
> Eddy Grabczewski
> [EMAIL PROTECTED]
>
>
>



---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Re: [SQL] PL/pgSQL syntax for strings

[Edward Grabczewski]

I've sorted this one thanks to John Berkus.
Thanks guys. I've included the solution below
for your interest.

===
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 cube (float,float,float, float,float,float);
CREATE FUNCTION cube (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;
  xz_   BOX;
  yz_   BOX;
  xy_   BOX;
  left  VARCHAR;
  right VARCHAR;
BEGIN
  left  := to_char(x1,''9.999'') || '','' ||
to_char(z1,''9.999'');
  right := to_char(x2,''9.999'') || '','' ||
to_char(z2,''9.999'');
  xz_   := left || '','' ||  right;

  left  := to_char(y1,''9.999'') || '','' ||
to_char(z1,''9.999'');
  right := to_char(y2,''9.999'') || '','' ||
to_char(z2,''9.999'');
  yz_   := left || '','' ||  right;

  left  := to_char(x1,''9.999'') || '','' ||
to_char(y1,''9.999'');
  right := to_char(x2,''9.999'') || '','' ||
to_char(y2,''9.999'');
  xy_   := left || '','' ||  right;

  INSERT INTO rtest(xz,yz,xy)
  VALUES (xz_, yz_, xy_);
  RETURN null;
END;'
LANGUAGE 'plpgsql';


SELECT cube(1,2,3,10,20,30);

SELECT * FROM rtest
ORDER BY xz USING <<;

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







---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[SQL] PL/pgSQL syntax for strings

[Edward Grabczewski]

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
[EMAIL PROTECTED]
===
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
[EMAIL PROTECTED]




---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])