Re: [SQL] Passing array to PL/SQL and looping
Is it possible to construct and return an array with plpgsql like.. CREATE FUNCTION test_array( ) RETURNS VARCHAR[] AS ' DECLARE return_array VARCHAR[]; BEGIN return_array[0] := ''test''; return_array[1] := ''test 1''; return_array[2] := ''test 2''; RETURN (return_array); END;' LANGUAGE 'plpgsql'; I get the following error when I try to run it: ERROR: parse error at or near "[" on line 4. If this worked I could clean up a LOT of hacky plpgsql code I have had to write. On Thu, 2002-09-26 at 18:20, Josh Berkus wrote: Peter, > I'm a newbie to PL/SQL and need help badly. I'm trying to pass my array of > id's to the function and then loop through until the array is empty. I know > there must be atleast five things I'm doing wrong. Simplified example: CREATE FUNCTION test_array ( INT[] ) RETURNS INT AS ' DECLARE id_array ALIAS for $1; count_it INT; BEGIN count_it := 1; WHILE id_array[count_it] LOOP count_it := count_it + 1; END LOOP; RETURN (count_it - 1); END;' LANGUAGE 'plpgsql'; returns the number of elements in the supplied array. -- Josh Berkus [EMAIL PROTECTED] Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) -- Greg Johnson <[EMAIL PROTECTED]>
Re: [SQL] Case Sensitive "WHERE" Clauses?
Jordan Reiter wrote: > Are string comparisons in postgresql case sensitive? > > I keep on having this response: > > SELECT * > FROM People > WHERE first_name='jordan' > > Result: 0 records > > SELECT * > FROM People > WHERE first_name='Jordan' > > Result: 1 record It's case-sensitive. You can do this: SELECT * FROM People WHERE lower(first_name) = 'jordon' Kevin ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] [SQL] function return multiply rows
En Fri, 27 Sep 2002 09:44:55 -0700 Joe Conway <[EMAIL PROTECTED]> escribió: > Jeroen Olthof wrote: > > vw_teams is a view but same problem when trying it on a single table > > CREATE FUNCTION test() RETURNS SETOF vw_teams AS 'select * from vw_teams;' > > LANGUAGE 'sql'; > > > > SELECT test(); > > > > results in > > > >test > > --- > > 137789256 > > 137789256 > > (2 rows) > > The capability to return composite types (multi-column rows) is limited in <= > PostgreSQL 7.2.x. What you are seeing are pointers to the rows, not the rows > themselves. While you are using the old versions, I believe you can retrieve the columns by calling them by name. At least this works on 7.2: create table vw_teams (a int, b int); insert into vw_teams values (1, 2); insert into vw_teams values (3, 4); select a(test()), b(test()); a | b ---+--- 1 | 2 3 | 4 (2 rows) I suspect the function is being evaluated twice for each row. Maybe there's a better way. -- Alvaro Herrera () La web junta la gente porque no importa que clase de mutante sexual seas, tienes millones de posibles parejas. Pon "buscar gente que tengan sexo con ciervos incendiánse", y el computador dirá "especifique el tipo de ciervo" (Jason Alexander) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Passing array to PL/SQL and looping
On Friday 27 September 2002 18:04, Josh Berkus wrote:
> Greg,
>
> > CREATE FUNCTION test_array( ) RETURNS VARCHAR[] AS '
> > DECLARE
> > return_array VARCHAR[];
> > BEGIN
> > return_array[0] := ''test'';
> > return_array[1] := ''test 1'';
> > return_array[2] := ''test 2'';
> > RETURN (return_array);
> > END;'
> > LANGUAGE 'plpgsql';
>
> No, it's not possible to do the above. This is a flaw in the current
> implementation of PL/pgSQL that will not be resolved until we attract
> some new Postgres hackers who really care about upgrading PL/pgSQL.
>
> Currently, if you want to use an array, it has to be passed as a
> parameter, or come from an external table. You cannot declare an
> Array data type. Annoying, really.
If I replace the return_array allocations in the above example with this
line:
return_array := ''{ test, test 1, test 2}'';
it _seems_ to work as expected, at least in 7.3b1., e.g.
test=> select array_dims(test_array) from test_array();
array_dims
[1:3]
(1 row)
Ian Barwick
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Passing array to PL/SQL and looping
> "Greg" == Greg Johnson <[EMAIL PROTECTED]> writes:
Greg> CREATE FUNCTION test_array( ) RETURNS VARCHAR[] AS '
Greg> DECLARE
Greg> return_array VARCHAR[];
Greg> BEGIN
Greg> return_array[0] := ''test'';
Greg> return_array[1] := ''test 1'';
Greg> return_array[2] := ''test 2'';
Greg> RETURN (return_array);
Greg> END;'
Greg> LANGUAGE 'plpgsql';
Greg> I get the following error when I try to run it:
Greg> ERROR: parse error at or near "[" on line 4.
I raised this issue when dealing with version 7.1 and the conclusion
was that PL/PgSQL doesn't understand array syntax. If you construct
the array as { val, val, val, ... } it works, but that is not a
practical solution for most uses.
What can we do to at least get this on the radar screen as a known
bug?
roland
--
PGP Key ID: 66 BC 3B CD
Roland B. Roberts, PhD RL Enterprises
[EMAIL PROTECTED] 76-15 113th Street, Apt 3B
[EMAIL PROTECTED] Forest Hills, NY 11375
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Passing array to PL/SQL and looping
Ian Barwick <[EMAIL PROTECTED]> writes:
> On Friday 27 September 2002 18:04, Josh Berkus wrote:
>> Currently, if you want to use an array, it has to be passed as a
>> parameter, or come from an external table. You cannot declare an
>> Array data type. Annoying, really.
> If I replace the return_array allocations in the above example with this
> line:
> return_array := ''{ test, test 1, test 2}'';
> it _seems_ to work as expected, at least in 7.3b1.
Yes, operations that work on whole-array values work fine in plpgsql.
It's accesses to array elements that aren't supported well. The main
case that seems to be missing in current sources is exactly assignment
to an array element, ie foo[n] := something.
More generally --- and this is really a shortcoming in our SQL language,
not specifically in plpgsql --- there isn't any way to construct an
array value as an expression result. I can imagine writing something
like
SELECT CAST(1,2,3 AS int[3]);
but we've got nothing like that at the moment. (If you don't see the
point of this as compared to
SELECT CAST('{1,2,3}' AS int[3]);
then think about replacing the 1, 2, and 3 by arbitrary integer
expressions.)
regards, tom lane
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Passing array to PL/SQL and looping
Roland Roberts <[EMAIL PROTECTED]> writes: > What can we do to at least get this on the radar screen as a known > bug? Oh, it's on the radar screen all right. Who wants to step up and fix it? regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Passing array to PL/SQL and looping
Tom Lane wrote: > Roland Roberts <[EMAIL PROTECTED]> writes: > > What can we do to at least get this on the radar screen as a known > > bug? > > Oh, it's on the radar screen all right. Who wants to step up and fix > it? Do we need a TODO for it? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] SQL formatter?
There's a tool called TXL that, given an SQL grammar can pretty print it. (a semantically aware code transformation tool is massive overkill for the job, but what the hell... :) Andrew Perrin wrote: >Unfortunately it is Windows based. The emacs mode for SQL is pretty >primitive too. Oh well - maybe I'll write one someday. > >Thanks, >Andy > >-- >Andrew J Perrin - http://www.unc.edu/~aperrin >Assistant Professor of Sociology, U of North Carolina, Chapel Hill >[EMAIL PROTECTED] * andrew_perrin (at) unc.edu > > >On Wed, 25 Sep 2002, Philip Hallstrom wrote: > > > >>Looks to be windows based, but... >> >>http://www.techno-kitten.com/PBL_Peeper/Online_Manual/SQL_Formatter/sql_formatter.html >> >>first hit when searching on google for "sql formatter". there were a lot >>of other options... >> >>You might look at how some of those C code indenter's work. Seems like >>some of them support multiple languages which means they maybe have some >>sort of "language definition" so maybe you could just write a sql one and >>it would just work. Of course I've never used one and don't know anything >>about it really so I could be wrong :) >> >>-philip >> >>On Wed, 25 Sep 2002, Andrew Perrin wrote: >> >> >> >>>Does anyone know of a routine for formatting SQL statements in a >>>structured way? Standalone or for emacs would be fine. I'm thinking of >>>something that could take a long SQL text statement and format it, e.g.: >>> >>>select foo from bar where baz and bop and not boo; >>> >>>becomes >>> >>>SELECT foo >>>FROM bar >>>WHERE baz >>> AND bop >>> AND NOT boo >>>; >>> >>>Thanks, >>>Andy >>> >>>-- >>>Andrew J Perrin - http://www.unc.edu/~aperrin >>>Assistant Professor of Sociology, U of North Carolina, Chapel Hill >>>[EMAIL PROTECTED] * andrew_perrin (at) unc.edu >>> >>> >>> >>>---(end of broadcast)--- >>>TIP 5: Have you checked our extensive FAQ? >>> >>>http://www.postgresql.org/users-lounge/docs/faq.html >>> >>> >>> >>---(end of broadcast)--- >>TIP 5: Have you checked our extensive FAQ? >> >>http://www.postgresql.org/users-lounge/docs/faq.html >> >> >> > > >---(end of broadcast)--- >TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] [GENERAL] CURRENT_TIMESTAMP
OK, we have two db's returning statement start time, and Oracle 8 not having CURRENT_TIMESTAMP. Have we agreed to make CURRENT_TIMESTAMP statement start, and now() transaction start? Is this an open item or TODO item? --- Manfred Koizar wrote: > On Tue, 24 Sep 2002 17:56:51 -0400 (EDT), Bruce Momjian > <[EMAIL PROTECTED]> wrote: > >Can you run a test: > > > > BEGIN; > > SELECT CURRENT_TIMESTAMP; > > wait 5 seconds > > SELECT CURRENT_TIMESTAMP; > > > >Are the two times the same? > > MS SQL 7: > begin transaction > insert into tst values (CURRENT_TIMESTAMP) > -- wait > insert into tst values (CURRENT_TIMESTAMP) > commit > select * from tst > > t > --- > 2002-09-24 09:49:58.777 > 2002-09-24 09:50:14.100 > > Interbase 6: > SQL> select current_timestamp from rdb$database; > > = > 2002-09-24 22:30:13. > > SQL> select current_timestamp from rdb$database; > > = > 2002-09-24 22:30:18. > > SQL> commit; > > Servus > Manfred > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] [GENERAL] CURRENT_TIMESTAMP
Martijn van Oosterhout wrote: > On Sat, Sep 28, 2002 at 11:28:03PM -0400, Bruce Momjian wrote: > > > > OK, we have two db's returning statement start time, and Oracle 8 not > > having CURRENT_TIMESTAMP. > > > > Have we agreed to make CURRENT_TIMESTAMP statement start, and now() > > transaction start? Is this an open item or TODO item? > > Well, I'd rather it didn't change at all. IMHO it's a feature, not a bug. In > any case, if it does get changed we'll have to go through the documentation > and work out whether we mean current_timestamp or now(). I think most people > actually want now(). Well, I think we have to offer statement start time somewhere, and it seems the standard probably requires that. Two other databases do it that way. Oracle doesn't have CURRENT_TIMESTAMP in 8.X. Can anyone test on 9.X? > Fortunatly where I work we only use now() so it won't really matter too > much. Is there a compelling reason to change? Yes, it will split now() and CURRENT_TIMESTAMP. I personally would be happy with STATEMENT_TIMESTAMP, but because the standard requires it we may just have to fix CURRENT_TIMESTAMP. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] [GENERAL] CURRENT_TIMESTAMP
Martijn van Oosterhout <[EMAIL PROTECTED]> writes: > On Sat, Sep 28, 2002 at 11:51:32PM -0400, Bruce Momjian wrote: >> Yes, it will split now() and CURRENT_TIMESTAMP. I personally would be >> happy with STATEMENT_TIMESTAMP, but because the standard requires it we >> may just have to fix CURRENT_TIMESTAMP. > Well, my vote would be for STATEMENT_TIMESTAMP. One problem with inventing STATEMENT_TIMESTAMP is that (if spelled that way, without parens) it would have to become a fully-reserved keyword, thus possibly breaking some applications that use that name now. But the real point, I think, is that the folks pushing for this think that the standard requires CURRENT_TIMESTAMP to be statement timestamp. Inventing some other keyword isn't going to satisfy them. I don't personally find the "it's required by the spec" argument compelling, because the spec specifically says that the exact behavior is implementation-dependent --- so anyone who assumes CURRENT_TIMESTAMP will behave as start-of-statement timestamp is going to have portability problems anyway. Oracle didn't seem to find the argument compelling either; at last report they have no statement-timestamp function. I'd be happier with the whole thing if anyone had exhibited a convincing use-case for statement timestamp. So far I've not seen any actual examples of situations that are not better served by either transaction timestamp or true current time. And the spec is perfectly clear that CURRENT_TIMESTAMP does not mean true current time... regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
