Re: [SQL] Function Syntax involving pipes and ' marks?

Ruben Gouveia wrote: Is that more expensive to run than just useing a bunch of ticks? Try wrapping your p_date in a quote_literal like ... 'where m.jb_date < '||quote_literal(p_date+INTEGER '1')||' and ... I personally have never noticed any increased overhead from quote_literal. -- Sent vi

Re: [SQL] Function Syntax involving pipes and ' marks?

Ruben Gouveia wrote: v_where varchar(256) := 'where m.jb_date < '||p_date + integer '1'|| ' and m.jb_date >='||p_date||''; Try wrapping your p_date in a quote_literal like ... 'where m.jb_date < '||quote_literal(p_date+INTEGER '1')||' and ... eg. CREATE OR REPL

Re: [SQL] Get day name(Mon, Tue... Sun) and day number (1, 2...7) from a date

Emi Lu wrote: Good morning, Could someone tell me the command to get the weekly day name and day number please. I am expecting something like: sql> select data_part('day name', current_date); sql> Monday sql> select data_part('day number', current_date); sql> 1 (Mon =1 ... Sun =7?) Thank

Re: [SQL] request for help with COPY syntax

Chuck D. wrote: Pardon me on this, the cat -A report for the failed line (and subsequent lines) shows ^M$ within the field, not just $. I assume that is probably a \r\n and postgres wants \r for field data and \n to end a line. I've tried working this over with sed but can't get the syntax

Re: [SQL] Regular Expressions

Ezequias R. da Rocha wrote: Hi list, I would like to know if postgresql has a Regular Expressions (Regex) implemented already. With it we could implement queries like Select * from myClientes where name = 'E[zs]equias' where the result occurs even if the field has Ezequias or Esequias. Reg

Re: [SQL] interval as hours or minutes ?

Aarni Ruuhimäki wrote: Hi all, Could anyone please tell an easy way to get total hours or minutes from an interval ? SELECT SUM(stop_date_time - start_date_time) AS tot_time FROM work_times WHERE user_id = 1; tot_time - 2 days 14:08:44 I'd like to have this like ... AS

Re: [SQL] to_chat(bigint)

Ezequias Rodrigues da Rocha wrote: Hi list, Now I noticed that it is impossible to convert a bigint field to char with the function to_char. Is it correct ? If not please tell me how to convert a bigint using to_char. Couple ways I can see immedately: select 123123123123123123123::BIGINT::

Re: [SQL] Using Control Flow Functions in a SELECT Statement

Ashish Ahlawat wrote: Hi Team I am unable to fetch data using following simple query it prompts following error *ORA: 00907: Missing right parenthesis* Query :- SELECT Name AS Title, StatID AS Status, RatingID AS Rating, IF(NumDisks>1, 'Check for extra disks!', 'Only 1 disk.') AS Verif

Re: [SQL] hiding column values for specific rows

Luca Ferrari wrote: Hi, I don't know if this's possible but I'd like to hide column values for specific rows within a query. Imagine I've got a table with columns username and password: users(username,password). Now I'd like the user registered in the table to see her password, to see who is r

Re: [SQL] spliting a row to make several rows

Gerardo Herzig wrote: Hi all: What a want to do is something like this: suppose i have this record aa--bb--cc I guess if im able to do some sql/plsql procedure to get something like it aa bb cc (3 records, rigth?) Thanks a lot Gerardo dev=#select split_to_rows('aa--bb--cc','--'); split_to_

Re: [SQL] hi let me know the solution to this question

Aaron Bono wrote: On 7/18/06, *Michael Fuhr* <[EMAIL PROTECTED] > wrote: http://www.postgresql.org/docs/8.1/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS OK, this question got me wondering: is there a way to determine, in a function/stor

Re: [SQL] "CASE" is not a variable

Keith Worthington wrote: "Keith Worthington" <[EMAIL PROTECTED]> writes: The following is a section of code inside an SQL function. On Wed, 28 Jun 2006 12:16:29 -0400, Tom Lane wrote SQL, or plpgsql? It looks to me like misuse of the plpgsql INTO clause (there can be only one).

Re: [SQL] Concat two fields into one at runtime

George Handin wrote: Is there a way using built-in PostgreSQL functions to combine two data fields into a single field at runtime when querying data? For example, the query now returns: idfirstlast --- --- -- 1 Goerge Handin 2 Joe Rachin I'd like it to re

Re: [SQL] Get max value from an comma separated string

Mauro Bertoli wrote: Hi, I've a field that contain values-comma-separated like A) 1;2;3;;5 -- ;2;;4;5 but also B) 12;34;18 how I can get the max value? For A I tried: SELECT max(array_upper(string_to_array(answer,';'),1)) FROM values; and work fine, but for B case I don't find a solution like SE

Re: [SQL] Query from shell

Owen Jacobson wrote: Judith wrote: Hi every body, somebody can show me hot to execute a query from a shell echo QUERY HERE | psql databasename Or, if you want to run several queries, run psql and run your queries there. or psql -d -c "your query here" ---(

Re: [SQL] Syntax for "IF" clause in SELECT

[EMAIL PROTECTED] wrote: Greetings, the following is an MySQL statement that I would like to translate to PostgreSQL: Could someone point me to a documentation of a coresponding Systax for an "IF" clause in the a SELECT, or is the some other way to do this select if(spektrum is null,' '

Re: [SQL] Arrays in PL/pgSQL routines?

Ken Winter wrote: Can arrays be declared in PL/pgSQL routines? If so, how? DECLARE try: my_array VARCHAR[] := '{}'; not sure if this works in 7.4 though, if that's the version that you are using. ---(end of broadcast)--- TIP 9: In versio

Re: [SQL] Defaulting a column to 'now'

Ken Winter wrote: How can a column’s default be set to ‘now’, meaning ‘now’ as of when each row is inserted? For example, here’s a snip of DDL: create table personal_data (… effective_date_and_time TIMESTAMP WITH TIME ZONE not null default 'now',… try with now(), instead of now ...

Re: [SQL] how to do a find and replace

Dawn Buie wrote: > I used: > > update media_instance set location=replace(location,'v.','') > where location like '%/0/v.%' > > and that did work- > > thank you very much. > > it seems to me that the replace function is the same as translate()- no? > Right, I forgot your WHERE clause. Some mo

Re: [SQL] how to do a find and replace

Dawn Buie wrote: > Hello- > I'm using postgres 7.4 > > I have a column of data with the wrong prefix for many items. > > The wrong entries are entered ' /0/v.myimage.jpg' > While the correct ones are ' /0/myimage.jpg' > > > I need to remove all the 'v.' characters from this column. > > > I'm

Re: [SQL] Tigger

Jaime Casanova wrote: > This seems bad to me also: > >>>CREATE FUNCTION session_update() RETURNS trigger AS $session_update$ >>>[..function body..] >>>$session_update$ LANGUAGE plpgsql; > > > I think it should be: > CREATE FUNCTION session_update() RETURNS trigger AS $$ > [..function body..] > $

Re: [SQL] Tigger

David Hofmann wrote: > I'm using 7.3. > >> From: Bricklen Anderson <[EMAIL PROTECTED]> >> To: David Hofmann <[EMAIL PROTECTED]> >> CC: pgsql-sql@postgresql.org >> Subject: Re: [SQL] Tigger >> Date: Fri, 22 Jul 2005 12:17:41 -0700 >> &

Re: [SQL] Tigger

David Hofmann wrote: > I've look throught the docs and from what I can see the bellow code > should work, however I keep getting the error: > > ERROR: parser: parse error at or near "$" at character 53 > > CREATE FUNCTION session_update() RETURNS trigger AS $session_update$ > BEGIN > -- Chec

Re: [SQL] Create connection with Oracle database from Postgres plpgsql

Dinesh Pandey wrote: > How can we create connection with Oracle database from Postgres plpgsql > function and execute some oracle stored procedure? > > Thanks > Dinesh You can use perl DBI to access Oracle, providing you have DBI and the plperlu language installed. Sample code that may help you g

Re: [SQL] UPDATEABLE VIEWS ... Examples?

Dmitri Bichko wrote: > warn "WARNING: dmitrisms are on, some assumptions may not make sense" beauty! :) -- ___ This e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations. Any distribution, use or copying of t

Re: [SQL] SQL command Error: "create table ... Like parentTable

Ying Lu wrote: Greetings, I have a simple question about SQL command : create table tableName1 LIKE parentTable INCLUDING defaults ; I was trying to create table "tableName1" with the same structure as "parentTable" without any data. I got a syntax error: 'syntax error at or near "like"

Re: [SQL] triggering an external action

Jay Parker wrote: I am trying to find the best way for a database trigger to signal a client process to take an action. Specifically, I am working on the classic problem of creating and modifying system accounts based on the updates to a "person registry" database. The basic model I'm working

Re: [SQL] Building a database from a flat file

Casey T. Deccio wrote: Question: is there an "easy" way to duplicate an existing schema (tables, functions, sequences, etc.)--not the data; only the schema? This way, I would only need to modify one schema (public) to make changes, and the build schema could be created each time as a duplicate of t

Re: [SQL] Junk queries with variables?

Steve - DND wrote: I don't know about pgAdmin, but in psql you can use \set: \set id 1 SELECT * FROM foo WHERE id = :id; \set name '\'Some Name\'' SELECT * FROM foo WHERE name = :name; Whenever I try the above I get an error at the backslash. Do I need to create a different language for this? Righ

Re: [SQL] MSSQL versus Postgres timing

Michael Fuhr wrote: On Tue, Feb 01, 2005 at 11:54:11AM -0500, Joel Fradkin wrote: A table with 645,000 records for associates has view (basically select * from tblassociates where clientnum = 'test') This is taking 13 seconds in postgres and 3 seconds in MSSQL. Please post the EXPLAIN ANALYZE outp