[postgis-users] Plpgsql function

[Obe, Regina]

EEHab,

Do you need to return the values or just insert them?  
If you don't need them returned it is  much easier and efficient to just
return boolean true, or if you need to return the values,
do as Alex suggested (change to RETURNS setof integer  and use a RETURN
NEXT syntax). 

 There are easier ways of doing it in PostgreSQL 8.3+ other than RETURN
NEXT (like RETURN query and RETURNING, but that doesn't quite work here
anyway).

Below is an example if you just need to insert the data

CREATE OR REPLACE FUNCTION eehab(mypoint integer) RETURNS boolean AS
$$
BEGIN

 
 INSERT INTO eehab (field1)  -- replace with whatever your field name is
 SELECT DISTINCT o 
	FROM ramadan 
	WHERE o=mypoint or
		a=mypoint 
	UNION SELECT DISTINCT a 
	FROM ramadan 
   WHERE o=mypoint or a=mypoint;
 
 return true;
 
 END;
 $$
 
 LANGUAGE 'plpgsql'

Hope that helps,
Regina


-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of alex
bodnaru
Sent: Thursday, February 19, 2009 7:22 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] Plpgsql function

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

hi eehab,

eehab hamzeh wrote:
> 
> Hello,
> 
> I have the following plpgsql function the output are 3 values. i need
to
> insert these values to the table.
> when i run the function, only the first value of the result are
inserted
> how i can insert the rest of the result to the table
> 
> CREATE OR REPLACE FUNCTION eehab(integer) RETURNS integer AS
> 
your function returns one integer. look at returning setof integer or
array in
plpgsql documentation.

> declare
> mypoint alias for $1;
> myresult integer;
> 
> begin
> 
> select into myresult distinct o from ramadan where o=mypoint or
> a=mypoint union select distinct a from ramadan where o=mypoint or
a=mypoint;
> 
> insert into eehab values (myresult);
> 
> return myresult;
> 
> end;
> 
> 'LANGUAGE 'plpgsql'
> 
> 
> kind regards
> 
> 
> 
>
------------------------------------------------------------------------
> Discover the new Windows Vista Learn more!
>
<http://search.msn.com/results.aspx?q=windows+vista&mkt=en-US&form=QBRE>
> 
> 
>
------------------------------------------------------------------------
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users

- --
- --
best regards,
alex
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iJwEAQECAAYFAkmd95IACgkQ2nA3WyrfyeOtEAP+KBQ/xoUwf33/zJfBajuabJjC
yo1nLEgugirv+/ZFGXTohwAUkeNtCWPTWO7ewcmizBqIRhXBsDiyx3EHxV4rEcUj
WErT7IECs+gaqBHLtGSeMH7ceUShVCWp4LgnP+yTvnZsbuYjadoELfL30YV/Tux5
yLML7BUen0WnfWN0dR8=
=0vFn
-----END PGP SIGNATURE-----
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
-----------------------------------------
The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and delete the material from any computer.