Re: [SQL] SELECT with Function

[Marcin Piotr Grondecki]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Paulo Nievierowski wrote:
| PS: Excuses my poor english.
Your english is excellent. MY is poor!!
| I create plpgsql function "myfunc(integer)" thats returns of type
| record with values from table X (the function decides what record must
| be select). The parameter is an keyprod existing in table X and in
| table A.
|
| It run well sending in psql:
| SELECT * FROM myfunc( 10006530 ) as (vret1 numeric, vret2 numeric);
|
| The problem is:
| I need return data from table A (this have column 'keyprod'). This
| value (keyprod) must be passed as parameter to myfunc(). The result
| must be a union of columns selected from table A and result of
| myfunc().
|
| How to create this query? It's possible?
Yep.
Look at this:
drop table dupa cascade;
create table dupa (a int, b int, c int);
insert into dupa (a, b, c) values (1, 2, 3);
insert into dupa (a, b, c) values (2, 3, 4);
create or replace function ttt(int) returns record  as '
declare
~r record;
begin
~select * into r from dupa where ($1=a);
~return r;
end;
' language 'plpgsql';
And then:
ojciec=# select * from ttt(1) as foo(aa int, bb int, cc int);
~ aa | bb | cc
- ++
~  1 |  2 |  3
Is this what you need?
- --
ojciec
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.4 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iD8DBQFBXYnyCAdl/NTXOsERAs/EAKCUq26LmG9N36vW/WXGC4i92Ci4VwCdF+eS
fiiHtfCVDONxxldr4SC17TI=
=tahv
-END PGP SIGNATURE-
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] SELECT: retrieve only 2 rows next to known row

[Marcin Piotr Grondecki]

Dnia Fri, Sep 09, 2005 at 04:23:00PM +0400.424.r. ([EMAIL PROTECTED]), Nikolay 
Samokhvalov napisal(a):
> Hi,
> 
> My knowledge of PostgreSQL's SQL is not good, but I know ISO/ANSI
> SQL:2003 (basics) quite well.
> 
> I've encountered with following task. I have one SELECT statement with
> ORDER BY clause; and know, that result set for this SELECT contains
> row with ID = 1000 (just for example).
> I don't know the position of this row in result set, but I want to
> retrieve 2 rows that are next to this  one.
> 
> First of all, I don't want (cannot) write PL/pgSQL function.
> 
> So, I should use CREATE TEMP SEQUENCE to associate all rows in result
> set with their order numbers (in MySQL. for example, I would use
> temporary variable num in SELECT: something like 'select @num := @num
> + 1', but here I cannot, can't I?)
> 
> Then, as I know, PostgreSQL doesn't support standard statement WITH,
> that probaby would help me with this task.
> 
> Any ways to solve this problem? Is it possible to make only one query?
> (at least with one row in result set - e.g., with the row _following_
> after my one)
> 
> I'd appreciate any help.
I've created some model of your problem.
CREATE TABLE "foo" ("id" serial, "val" text); -- (this "model" is named "foo", 
as you see :]).
Then I've inserted some values; 'SELECT * FROM "foo" ORDER BY ("val")' gives 
now:
 id | val
+-
  4 | a
  2 | b
  3 | c
  1 | d
  5 | e
  7 | f
  6 | g
Now we'd like to "find" row where val='d' and this row neighbours.
This question results with our "center" row and next one:
SELECT * FROM "foo" WHERE ("val">='d') ORDER BY ("val") LIMIT 2;
Previous row:
SELECT * FROM "foo" WHERE ("val"<'d') ORDER BY ("val") DESC LIMIT 1;
Is it expected result in task similar to yours?
Ah, 'Is it possible to make only one query?'.
Yep, by "unioning" two given questions:
(SELECT * FROM "foo" WHERE ("val">='d') ORDER BY ("val") LIMIT 2) UNION 
ALL (SELECT * FROM "foo" WHERE ("val"<'d') ORDER BY ("val") DESC LIMIT 1) ORDER 
BY ("val");
Effect:
 id | val
+-
  3 | c
  1 | d
  5 | e
As desired.
Now we can change field/s to watch values for (we were looking into "val", now 
we'd like to do same work on "id"). 
(SELECT * FROM "foo" WHERE ("id">='5') ORDER BY ("id") LIMIT 2) UNION 
ALL (SELECT * FROM "foo" WHERE ("id"<'5') ORDER BY "id" DESC LIMIT 1) ORDER BY 
("id");
It gives: 
 id | val
+-
  4 | a
  5 | e
  6 | g
Once again result as expected in our dreams! :]]]

Change table into desired one, change ordering into desired one and... your 
problem'll be solved, I hope... :]?

regards
-- 
Marcin Piotr Grondecki


pgpG5iHUzmcVy.pgp
Description: PGP signature