Re: [SQL] Como ejecutar una funcion insert en plpgsql....
Fernando -- I have forwarded your message to the postgres SQL list where it started -- it is good practice to "cc" the list in general so that others can contribute / learn. If you could provide the version of postgres you are using that might help, as there differences between 7.x and 8.x that might matter. I am at the very end of my day and too tired to be coherent -- perhaps someone else can provide an elegant snippet of code ? If there's no response in the next few hours I'll give it try. ZzzzZZzzz G -Original Message- From: Fernando Garcia [mailto:[EMAIL PROTECTED] Sent: Thu 11/10/2005 5:23 AM To: Gregory S. Williamson Cc: Subject:Re: [SQL] Como ejecutar una funcion insert en plpgsql thanks very much, jeje, my english its very rusry... i try explain to you what i can to do in the first question.remember I try! Well, I have one function what insert one record in one tabe (EJ: adduser(name,email,password)) How can I execute this function to prove this insertion..??? Welll, you understand somethin in muy very bad english!!! OK Thanks anywere.. On 11/9/05, Gregory S. Williamson <[EMAIL PROTECTED]> wrote: > > > Fernando -- > > I am not sure about the first question -- my spansih is rusry. > > postgres does force all column, table and schema names (I think) to lower > case (there have been recent long discussions about this on this list IIRC). > If you want to preserve case put the column name in double quotes: > "ItemID" = ItemID > ItemID = itemid > > This applies both to the creation of a table and when referring to that > table's columns. > > HTH, > > Greg Williamson > DBA > GlobeXplorer LLC > > -Original Message- > From: [EMAIL PROTECTED] on behalf of Fernando Garcia > Sent: Wed 11/9/2005 6:22 AM > To: pgsql-sql@postgresql.org > Cc: > Subject: [SQL] Como ejecutar una funcion insert en plpgsql > Hola a todo... necesito ejecutar una consulta Insert que realice en pgsql > para ver si inserta correctamente en la tabla asociada, pero con execute > me > da un erroralguien me puede decir como lo hago > yo trabajo con el editor postgresql manager pro.. > Gracias... > Ahh otra cosa: en otra consulta que realice pero que es un select al > principio me daba un error porque no encontraba la columna "ItemID" la > cual > porsupuesto existia, entonces a la columna le quite las mayusculas de su > nombre quedando "itemid" y ya no me salio el error.en postgres los > nombres de tablas y columnas deben ser siempre con minuscula > > > > > > > !DSPAM:43734556223684784314229! ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] time
Hello everybody!!!, I'd like to know if there another way to get the time from a timestamp type, because in earliest versions I just get the time like time(fecha) and now this returns an error, thanks in advanced!!!
[SQL] how to update table to make dup values distinct
[PostgreSQL 7.4RC2 on i686-pc-linux-gnu](I know, I know... must upgrade soon) I have a table mytable like: i | txt ---+--- 1 | the 2 | the 3 | rain 4 | in 5 | mainly 6 | spain 7 | stays 8 | mainly 9 | in I want to update it, adding a ':' to txt so that each txt value is unique. I don't care which entry gets changed. I tried: update mytable set txt=mytable.txt || ':' from mytable t2 where mytable.txt=t2.txt and mytable.i=t2.i; but this updated both duplicated entries. Um, there may sometimes be 3 or 4 duplicates, not just two. For these, I can add multiple colons, or one each of an assortment of characters, say ':+*&^#'. Performance does not matter here. The real table has 30K rows, ~200 dups. To clarify, I want to end up with something like: 1 | the 2 | the: 3 | rain 4 | in 5 | mainly: 6 | spain 7 | stays 8 | mainly 9 | in: -- George -- "Are the gods not just?" "Oh no, child. What would become of us if they were?" (CSL) ---(end of broadcast)--- TIP 1: 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] RETURNS SETOF table; language 'sql'
I think its cause you changed your procedure from being written in SQL to being writtern in PLPGSQL in your second implementation Sets of records are returned from a PLPGSQL function with a RETURN statement ... not a SELECT... Check out the sections of the manual that talk about PLPGSQL 35.7.1. Returning From a Function There are two commands available that allow you to return data from a function: RETURN and RETURN NEXT. 35.7.1.1. RETURN RETURN expression;RETURN with an expression terminates the function and returns the value of expression to the caller. This form is to be used for PL/pgSQL functions that do not return a set. When returning a scalar type, any expression can be used. The expression's result will be automatically cast into the function's return type as described for assignments. To return a composite (row) value, you must write a record or row variable as the expression. The return value of a function cannot be left undefined. If control reaches the end of the top-level block of the function without hitting a RETURN statement, a run-time error will occur. If you have declared the function to return void, a RETURN statement must still be provided; but in this case the expression following RETURN is optional and will be ignored if present. 35.7.1.2. RETURN NEXT RETURN NEXT expression;When a PL/pgSQL function is declared to return SETOF sometype, the procedure to follow is slightly different. In that case, the individual items to return are specified in RETURN NEXT commands, and then a final RETURN command with no argument is used to indicate that the function has finished executing. RETURN NEXT can be used with both scalar and composite data types; in the latter case, an entire "table" of results will be returned. "Mario Splivalo" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > When I issue something like this: > > SELECT * FROM ads WHERE id=1004; > > i get: > > id | vpn_id | service_id | ignore_length | start_time | > end_time| ad_text > --+++---+++ > 1004 | 1 |106 | f | 2005-01-01 00:00:00+01 | > 2005-12-31 00:00:00+01 | Probna reklama numera una! > > > Now, I create a function that does that: > > CREATE FUNCTION get_ads(int4) > RETURNS SETOF ads > AS > 'SELECT * FROM ads WHERE id=$1' > LANGUAGE 'sql' > > When I do: > > SELECT * FROM get_ads(1004); > > i get: > > ERROR: query-specified return row and actual function return row do not > match > > Why is that? > > Mike > > P.S. That's run on Postgres 7.4. > -- > Mario Splivalo > Mob-Art > [EMAIL PROTECTED] > > "I can do it quick, I can do it cheap, I can do it well. Pick any two." > > > > ---(end of broadcast)--- > TIP 1: 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 > ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] migratation of database from oracle9i to postgreSQL8.0.3
There are some tools.. search about Ora2PG or so One other way to go is to have the Oracle DB dumped out as text and change/review the statements to suit your new structure and PG differences You definitely need mass changing tools with regular expressions. On 11/9/05, zenith Das <[EMAIL PROTECTED]> wrote: > > Hi > Can anyone help me out > How can i migrate database from oracle9i to postgreSQL8.0.3 where the table > structure may differ in certain way... > > ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] time
Hello everybody!!!, I'd like to know if there another way to get the time from a timestamp type, because in earliest versions I just get the time like time(fecha) and now this returns an error, thanks in advanced!!!
Re: [SQL] time
On Thu, 2005-11-10 at 09:03 -0600, Judith Altamirano Figueroa wrote:
> Hello everybody!!!, I'd like to know if there another way to get the
> time from a timestamp type, because in earliest versions I just get
> the time like time(fecha) and now this returns an error, thanks in
> advanced!!!
rbt=# select cast(now() - date_trunc('day', now()) as time);
time
-
13:10:42.495579
(1 row)
--
---(end of broadcast)---
TIP 1: 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] time
On Thu, Nov 10, 2005 at 01:11:27PM -0500, Rod Taylor wrote:
> rbt=# select cast(now() - date_trunc('day', now()) as time);
> time
> -
> 13:10:42.495579
> (1 row)
Am I missing something? Is there a reason not to simply cast the
timestamp value to time?
test=> select cast(now() - date_trunc('day', now()) as time);
time
-
11:19:19.8921250105
(1 row)
test=> select now()::time;
now
-
11:19:19.892125
(1 row)
test=> select cast(now() as time);
now
-
11:19:19.892125
(1 row)
--
Michael Fuhr
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [SQL] time
On Thu, 2005-11-10 at 11:21 -0700, Michael Fuhr wrote:
> On Thu, Nov 10, 2005 at 01:11:27PM -0500, Rod Taylor wrote:
> > rbt=# select cast(now() - date_trunc('day', now()) as time);
> > time
> > -
> > 13:10:42.495579
> > (1 row)
>
> Am I missing something? Is there a reason not to simply cast the
> timestamp value to time?
Nope. I had thought that the question came up because a straight cast
didn't work so I gave the first work around I thought of.
--
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] time
[Please copy the mailing list on replies.] On Thu, Nov 10, 2005 at 12:24:17PM -0600, Judith Altamirano Figueroa wrote: > excuse me and how can I just get the hour, minute and second The time type takes an optional precision: test=> select now()::time; now - 11:36:34.124678 (1 row) test=> select now()::time(0); now -- 11:36:34 (1 row) test=> select now()::time(2); now - 11:36:34.12 (1 row) -- Michael Fuhr ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] time
Michael Fuhr <[EMAIL PROTECTED]> writes: > Am I missing something? Is there a reason not to simply cast the > timestamp value to time? > test=> select now()::time; >now > - > 11:19:19.892125 > (1 row) > test=> select cast(now() as time); >now > - > 11:19:19.892125 > (1 row) I think the OP was trying to use the functional cast syntax time(now()) which worked long ago, but has not since we added the SQL-spec time precision syntax. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] time
On Thu, Nov 10, 2005 at 02:13:43PM -0500, Tom Lane wrote: > Michael Fuhr <[EMAIL PROTECTED]> writes: > > Am I missing something? Is there a reason not to simply cast the > > timestamp value to time? > > I think the OP was trying to use the functional cast syntax > time(now()) > which worked long ago, but has not since we added the > SQL-spec time precision syntax. I was referring to the expression that Rod Taylor posted, wondering if there was some subtlety I was missing or if Rod simply didn't think of the simpler solution (Rod responded indicating the latter). -- Michael Fuhr ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] time
So I don't open a new thread.. I have a table with a column of type TIMESTAMP. In output, I need to format it.. what's the best way to do it? So, for instance, how could I format it so that it would output as YY-MM-DD HH:MM? Regards, Tadej -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Tom Lane Sent: 10. november 2005 20:14 To: Michael Fuhr Cc: Rod Taylor; Judith Altamirano Figueroa; pgsql-sql@postgresql.org Subject: Re: [SQL] time Michael Fuhr <[EMAIL PROTECTED]> writes: > Am I missing something? Is there a reason not to simply cast the > timestamp value to time? > test=> select now()::time; >now > - > 11:19:19.892125 > (1 row) > test=> select cast(now() as time); >now > - > 11:19:19.892125 > (1 row) I think the OP was trying to use the functional cast syntax time(now()) which worked long ago, but has not since we added the SQL-spec time precision syntax. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] time
On Thu, Nov 10, 2005 at 08:29:07PM +0100, Tadej Kanizar wrote: > I have a table with a column of type TIMESTAMP. > In output, I need to format it.. what's the best way to do it? > So, for instance, how could I format it so that it would output as YY-MM-DD > HH:MM? See to_char() in the "Data Type Formatting Functions" section of the "Functions and Operators" chapter in the documentation. http://www.postgresql.org/docs/8.0/interactive/functions-formatting.html -- Michael Fuhr ---(end of broadcast)--- TIP 1: 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
[SQL] High level discussion for design of using ACL to retrieve Data
We currently use a system with 4 tables (division, region, district, location). The data for the most part has a field named location id. The users get a level and location id (if they are district it would represent a district id etc). I have been asked to make this more flexible, for example if the user needs data for two locations but they are not in the same district. One thing I have had to add was the ability to have a user have access to a location and then also have access to a related location (main and auto center have different location number, but if the user has access to the main location he can also see auto center data). I did this with a xref table and a union, but it seem pretty slow. So adding even more flexibity like multiple districts, locs etc (was thinking of trying to do some kind of grouping that would encompass our current plan) has been a problem I have thought about a lot, but I have not figured out a way that will give fast access. I could do groups of access rights and do unions with distinct to get data, but I fear that would be really slow. Any one have ideas on this subject? Thanks in advance. Joel Fradkin
Re: [SQL] how to update table to make dup values distinct
On Thu, Nov 10, 2005 at 10:58:18 -0500, george young wrote: > [PostgreSQL 7.4RC2 on i686-pc-linux-gnu](I know, I know... must upgrade soon) > > I have a table mytable like: > i | txt > ---+--- > 1 | the > 2 | the > 3 | rain > 4 | in > 5 | mainly > 6 | spain > 7 | stays > 8 | mainly > 9 | in > > I want to update it, adding a ':' to txt so that each txt value is unique. > I don't care which entry gets changed. I tried: This seems like an odd way to fix whatever problem you are having. Assuming you really do want to go through with this, you can use oids to distinguish rows. For example you could add a colon to the row with the lowest oid for each repeated string, and keep doing that until you have them all fixed. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
