Thread: Function

Hello,

I have a problem with the output value of a function, that is to say,
the value I get when the function is carried out:
I want a function, chich output value is an array, and when I create the
function, it is defined as follows:

> create function path_inte(path,path) returns _point
>  as '/net/ipfy/angel/postgres/geometry/libMWgeom.so' language 'c';
>
>  When the function is used, the resultat is always: "{}", so I do not
> Know if:

-the function is not well defined, or
-the output value can not be an array

Thank you very much.



Angel Manuel Diaz Aunion
Institut fuer Photogrametrie und Fernerkundung.
Universitaet Karlsruhe.

Angel Manuel Diaz Aunion <angel@ipf.uni-karlsruhe.de> writes:
> I want a function, chich output value is an array, and when I create the
> function, it is defined as follows:

>> create function path_inte(path,path) returns _point
>> as '/net/ipfy/angel/postgres/geometry/libMWgeom.so' language 'c';
>> 
>> When the function is used, the resultat is always: "{}", so I do not
>> Know if:
> -the function is not well defined, or
> -the output value can not be an array

I don't know of any reason why a C function couldn't return an array,
so the problem is probably in your code...
        regards, tom lane

Hi,

In ORACLE I can perform the following query:

SELECT tableA.id, title, qty
FROM tableA, (SELECT id, count(*) qty FROM tableB group by id) tableC
WHERE tableA.id = tableC.id (+)

but in PostgreSQL I get error message.
How can I perform it in Postgres?

thanks,
robert

"Robert Nosko" <robertn@eori.net.pl> writes:
> In ORACLE I can perform the following query:

> SELECT tableA.id, title, qty
> FROM tableA, (SELECT id, count(*) qty FROM tableB group by id) tableC
> WHERE tableA.id = tableC.id (+)

> but in PostgreSQL I get error message.

We don't currently have sub-SELECTs in FROM (maybe in 7.1 though).
I doubt we ever will support that nonstandard "(+)" syntax (that's
Oracle-speak for an outer join, no?).

But 7.0 does handle sub-SELECTs in the result list, so you could do

SELECT id, title,      (SELECT count(*) FROM tableB where tableB.id = tableA.id) AS qty
FROM tableA;

This will be slower than the other way, probably, but it works and is
at least as readable.
        regards, tom lane

Any ideas on how I might speed this up? I know sub-selects are seriously
slow, I'm not sure what else can be done though.

DELETE from applicants_states WHERE app_id IN (SELECT s.app_id FROM
applicants_states AS s, applicants AS a WHERE s.app_id=a.app_id AND
(a.created + '90 days') < 'now' AND a.resubmitted < '10-03-1999')

Thanks!

- Mitch

"Mitch Vincent" <mitch@venux.net> writes:
> Any ideas on how I might speed this up? I know sub-selects are seriously
> slow, I'm not sure what else can be done though.

> DELETE from applicants_states WHERE app_id IN (SELECT s.app_id FROM
> applicants_states AS s, applicants AS a WHERE s.app_id=a.app_id AND
> (a.created + '90 days') < 'now' AND a.resubmitted < '10-03-1999')

I believe you'd get the same result from

DELETE FROM applicants_states
WHERE app_id = applicants.app_id AND     (applicants.created + '90 days') < 'now' AND     applicants.resubmitted <
'10-03-1999';

This is not SQL-standard; doing an implicit join when you mention
another table in WHERE is a leftover from Berkeley Postquel.  But
it solves this sort of problem rather handily.

If you want to stick to portable SQL, I'd at least suggest getting rid
of the unnecessary join in the subselect; wouldn't

DELETE from applicants_states WHERE app_id IN (SELECT app_id   FROM applicants   WHERE (created + '90 days') < 'now'
ANDresubmitted < '10-03-1999');
 

produce the same results?

Also, 7.0 does uncorrelated subselects (like this one) somewhat
faster than prior releases, so just upgrading might solve the problem
for you.
        regards, tom lane