[SQL] batch file
Hi all!, I want to execute several SQL statements one after another. Does anyone know if it is possible to create something like a "batch file" which contains all SQL commands I want. Best Regards: Milen ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Prepared statements in PGSQL functions
Hi Listers,
I want to use prepared statement in a function. Here is my code:
create or replace function generate_data
( integer, integer )
returns integer
as
$BODY$
declare
p_count alias for $1;
p_max_value_id1 alias for $2;
v_max_value_id1 integer ;
v_id1 int;
v_id2 int;
v_filler varchar(200) := repeat('BIGSTRING', 3);
begin
v_id1:= round( (random()* v_max_value_id1)::bigint,0);
v_id2:= round( (random()* v_max_value_id1)::bigint,0);
prepare mystmt( int, int, varchar) as insert into part
values ($1,$2,$3);
execute mystmt(v_id1, v_id2, v_filler );
deallocate mystmt;
end;
$BODY$
language plpgsql ;
Definition of table part is :
CREATE TABLE part (
id1int not null,
id2int not null,
filler varchar(200)
);
When I try to call my function I am getting the following errors :
postgres=# select * from gen (10, 10 );
ERROR: function mystmt(integer, integer, character varying) does not exist
HINT: No function matches the given name and argument types. You may need to
add explicit type casts.
CONTEXT: SQL statement "SELECT mystmt( $1 , $2 , $3 )"
PL/pgSQL function "gen" line 12 at execute statement
How to solve my problem ? Is it possible at all to call prepared statement
inside a function at all?
Regards. MILEN
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [SQL] Prepared statements in PGSQL functions
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Tom Lane Sent: Wednesday, June 14, 2006 4:35 PM To: Milen Kulev Cc: [email protected] Subject: Re: [SQL] Prepared statements in PGSQL functions "Milen Kulev" <[EMAIL PROTECTED]> writes: >> I want to use prepared statement in a function (your comments below). Wanted just to test the difference ... Why? You seem not to be aware that plpgsql implicitly prepares statements behind the scenes. &&>> I already have a version with "direct" insert ( just as you say a couple of lines below) > prepare mystmt( int, int, varchar) as insert into part > values ($1,$2,$3); > execute mystmt(v_id1, v_id2, v_filler ); > deallocate mystmt; If that worked it would be *exactly* the same as just doing insert into part values (v_id1, v_id2, v_filler); except for being slower due to re-preparing each time through the function. So don't waste your time trying to outsmart the language. >> My idea was to prepare the statment once and execute it in a loop many >> times (within a procedure/function). Anyway, obviously there is no performance gain in using prepared statement in functions. Regards. Milen regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] UTF-8 Problem ?
Hi Listers, I want to insert some german specific characters (umlaut characters) into a table, but I am getting the following Error message: postgres=# EXECUTE stmt (1, 1 , 1 , 'Grün') ; ERROR: invalid UTF-8 byte sequence detected near byte 0xfc Or postgres=# EXECUTE stmt (1, 1 , 1 , 'MAßßtab') ; ERROR: invalid UTF-8 byte sequence detected near byte 0xdf Here are my object/statement definitions : A) PREPARE stmt( int, int, int, varchar) as insert INTO part values ($1,$2,$3,$4); B) postgres=# \d+ part Table "public.part" Column | Type | Modifiers | Description ++---+- id1| integer| not null | id2| integer| not null | id3| integer| not null | filler | character varying(200) | | C) postgres=# l\l List of databases Name| Owner | Encoding +---+--- db1| user1 | SQL_ASCII postgres | pg| UTF8 template0 | pg| UTF8 template1 | pg| UTF8 How to solve my problem ? Best Regards. Milen ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] UTF-8 Problem ?
Hi Thomas, What actually the compile option --enable-recode is doing ? I haven't compiled PG with this option for sure (perhaps is the option On by defalt ?), but oyu advice hepled me: postgres=# \encoding UTF8 postgres=# \encoding UTF8 postgres=# SET client_encoding = 'LATIN1'; SET postgres=# \encoding LATIN1 postgres=# PREPARE stmt( int, int, int, varchar) as insert INTO part values ($1,$2,$3,$4); PREPARE postgres=# EXECUTE stmt (1, 1 , 1 , 'MAßßtab') ; INSERT 0 0 postgres=# EXECUTE stmt (1, 1 , 1 , 'MAßßtab') ; INSERT 0 0 postgres=# EXECUTE stmt (1, 1 , 1 , 'Grün') ; INSERT 0 0 postgres=# postgres=# SELECT filler from part where filler like 'MA%' or filler like 'Gr%' ; filler - MAßßtab MAßßtab Grün (3 rows) Regards. Milen -Original Message- From: Thomas Beutin [mailto:[EMAIL PROTECTED] Sent: Thursday, June 15, 2006 2:45 PM To: [email protected] Cc: Milen Kulev Subject: Re: [SQL] UTF-8 Problem ? Hi Milen, Milen Kulev wrote: > Hi Listers, > I want to insert some german specific characters (umlaut characters) > into a table, but I am getting the following > Error message: > postgres=# EXECUTE stmt (1, 1 , 1 , 'Grün') ; > ERROR: invalid UTF-8 byte sequence detected near byte 0xfc > > Or > > postgres=# EXECUTE stmt (1, 1 , 1 , 'MAßßtab') ; > ERROR: invalid UTF-8 byte sequence detected near byte 0xdf > > Here are my object/statement definitions : > > A) PREPARE stmt( int, int, int, varchar) as insert INTO part values > ($1,$2,$3,$4); > > B) > postgres=# \d+ part > Table "public.part" > Column | Type | Modifiers | Description > ++---+- > id1| integer| not null | > id2| integer| not null | > id3| integer| not null | > filler | character varying(200) | | > > C) > > postgres=# l\l >List of databases > Name| Owner | Encoding > +---+--- > db1| user1 | SQL_ASCII > postgres | pg| UTF8 > template0 | pg| UTF8 > template1 | pg| UTF8 > > > How to solve my problem ? You should insert only correct utf8 strings or set the client encoding correctly: SET client_encoding = 'LATIN1'; or SET client_encoding = 'LATIN9'; IIRC postgresql must be compiled with --enable-recode to support this. Regards, -tb ---(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
[SQL] Difficulties with a master-detail query
Hi, list! You have most probably met the same problem: I have a master table, describing the objecs I'm interested in - let's say employees. I have a details table, defining all possible values of one of the properties of the objects - let's say languages spoken. And of course I have a table desribing the table the connection between the latter two - N:N (fairly standard solution). Here is the scheme: CREATE TABLE employee ( employee_id serial PRIMARY KEY, name varchar(20) ); CREATE TABLE skill ( skill_id serial PRIMARY KEY, name varchar(20) ); CREATE TABLE employee_skill ( employee_id integer, skill_id integer, CONSTRAINT employee_skill_employee_id_fkey FOREIGN KEY (employee_id) REFERENCES employee(employee_id), CONSTRAINT employee_skill_skill_id_fkey FOREIGN KEY (skill_id) REFERENCES skill (skill_id), CONSTRAINT employee_skill_employee_id_key UNIQUE (employee_id, skill_id) ); I would like to get all employees, who speak two specified languages (say german and french). The following query gives me that, bu I don't like it (see for yourself): SELECT е.employee_id, е.name, COUNT(s.skill_id) FROM employee AS e INNER JOIN employee_skill AS es ON e.employee_id=es.employee_id INNER JOIN skill AS s ON s.skill_id=es.skill_id AND s.skill_id IN (1, 2) GROUP BY e.employee_id, e.name HAVING COUNT(s.skill_id)>=2; Here "(1, 2)" are the IDs for those predefined two languages, got from the "skill" table. Аnd that two in "COUNT(s.skill_id)>=2" is there because the count of the languages. Any ideas for simpler and more universal query? Please CC me, because I'm not subscribed. -- Milen A. Radev ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] new rule syntax?
A. R. Van Hook написа: I have two tables defined as: checks (ckidint NOT null PRIMARY KEY, payto text, notes text, ckdate date, printed int default 0, tdate timestamp not null) checkitems (itemint not null, ckidint NOT null references checks, itemtypeint not null, amt numeric(7,3), primary key (item, ckid)) in previous versions (<8.1) the following rule declaration seemed to work fine create rule checks_d0 as on delete to checks do delete from checkitems where ckid = checks.ckid; in 8.1.2 I get ERROR: missing FROM-clause entry from table "checks" any idea? May be you are bitten by the change of the default value of "add_missing_from" setting (http://www.postgresql.org/docs/8.1/static/runtime-config-compatible.html#RUNTIME-CONFIG-COMPATIBLE-VERSION). -- Milen A. Radev ---(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] Interval subtracting
Milorad Poluga написа: Hi all, Is there something incorrect in the above query ? SELECT '10 years 1 mons 1 days'::interval - '9 years 10 mons 15 days'::interval ?column? --- 3 mons -14 days Why not '2 mons 16 days' ? /version = PostgreSQL 8.0.4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.2 20041017 (Red Hat 3.4.2-6.fc3) / How many days are there in a month? -- Milen A. Radev ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Interval subtracting
Milorad Poluga написа:
> On Saturday 18 February 2006 15:24, Milen A. Radev wrote:
>> Milorad Poluga написа:
>>> Hi all,
>>>
>>> Is there something incorrect in the above query ?
>>> SELECT '10 years 1 mons 1 days'::interval - '9 years 10 mons 15
>>> days'::interval
>>>
>>> ?column?
>>> ---
>>> 3 mons -14 days
>>>
>>> Why not '2 mons 16 days' ?
>>>
>>> /version = PostgreSQL 8.0.4 on i686-pc-linux-gnu, compiled by GCC gcc
>>> (GCC) 3.4.2 20041017 (Red Hat 3.4.2-6.fc3) /
>>
>> How many days are there in a month?
>>
>
> I beleive that a month is calculated on the 30-days base.
Are you sure? Where?
>
> One way to solve this problem is to use a neutal date element and make
> timestamps :
>
> SELECT age(('1990-01-01'::date + '10 years 1 mons 1
> days'::interval)::timestamp ,
> ('1990-01-01'::date + '9 years 10 mons 15 days'::interval)::timestamp)
>
> age
> --
> 2 mons 16 days
>
Please read the last paragraph in section 8.5.1.4 of the manual
(http://www.postgresql.org/docs/8.1/static/datatype-datetime.html#AEN4775)
. It mentions the functions named "justify_days" and "justify_hours"
that could do what you need.
--
Milen A. Radev
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] Counting the rows INSERTed/UPDATEd?
Mario Splivalo написа: > I have found, I thinl, in the pg manual, the way to get the number of > rows inserted/updated, from within the plpgsql. I can't find it anymore, > is that still there, or I misread something earlier? > > Mario May be this one? http://www.postgresql.org/docs/8.1/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS -- Milen A. Radev ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] How to find entries missing in 2nd table?
Richard Broersma Jr написа: >> Hi, >> I realize I probably lost my marbles but I've been having a god >> awful time with a single query: >> >> control: >> >> >> controller_id pk; >> >> >> datapack: >> >> controller_id fk; >> >> >> >> >> I need to get all entries from the table control that are not listed in >> datapack. > > SELECT C.CONTROLLER_ID > > FROM CONTROL AS C > LEFT JOIN DATAPACK AS D ON ( C.CONTROLLER_ID = D.CONTROLLER_ID) > > WHERE D.CONTROLLER_ID IS NULL; > Or (SELECT controller_id FROM control) EXCEPT (SELECT controller_id FROM datapack) ? -- Milen A. Radev ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] selecting rows tagged with "a" but not "b"
[email protected] написа: Hi, I have a two tables: article articleID, name, content tags articleID, tag I want to find all articles that are tagged with "a" but not "b" how do I do this? what I'd like to do is: select articleID from tags where tag="a" SUBTRACT select articleID from tags where tab="b" how do I do this in real SQL? Replace "SUBSTRACT" with "EXCEPT" (http://www.postgresql.org/docs/current/static/sql-select.html#SQL-EXCEPT). -- Milen A. Radev -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Triggers using PL/pgSQL
Thusitha Kodikara написа: > Hello, > > I am interested in developing some triggers to keep track of records that are > changed (possibly the changes of one or more specific columns). In addition > to keeping the new values, I also need to keep the old values (may be on a > separate table). > > Though I have done similar things in other RDBMs using SQL, I find doing > this in Postgres, a little bit complicated - may be because it needs to be > done through a separate procedural language and through a separate function. > The Postgres documentation also didn't provide much help ( the examples in > C). I was mainly looking for example showing how to refer 'OLD' and 'NEW' > rows using PL/pgSQL. > > Can someone please direct me to some such examples? http://www.postgresql.org/docs/8.1/static/plpgsql-trigger.html -- Milen A. Radev ---(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] Mac Address
Ezequias Rodrigues da Rocha написа: > Hi list, > > It is possible to retrieve the MAC Address of a computer using a Postgresql > function like others: > > http://www.postgresql.org/docs/8.1/static/functions-info.html > > I can retrieve a ip of some statement connection but I didn't find how to > get the MAC Address. Is it possible ? No - the client and the server communicate using TCP/IP as transport/internet layer but that does not mean they use Ethernet as network access layer. -- Milen A. Radev ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Omission of 'FROM' ? diff between pgsql 7.4.1 and pgsql 8.1.4
[EMAIL PROTECTED] написа: [...] > Is there some query optimization config setting I can set in pgsql 8.1.4 to > fix > this problem rather than having to change the queries in the code? I've read > over the changes between pgsql 7 and pgsql 8 but nothing seems to stand out to > me as referencing this problem. Anyone with more knowledge that can point me > in > the right direction? Look here for "add_missing_from" - http://www.postgresql.org/docs/8.1/static/runtime-config-compatible.html#RUNTIME-CONFIG-COMPATIBLE-VERSION -- Milen A. Radev ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Help with quotes in plpgsql
Richard Ray написа: > How should this be properly quoted > > create or replace function test(integer) returns setof text as $$ > declare > a record; > begin > select into a now() - interval '$1 day'; > return next a; > return; > end > $$ language 'plpgsql'; > > I'm not having a lot of luck Welcome to psql 8.2.0, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit milen=> create or replace function test(integer) returns setof text as $$ milen$> declare milen$> a record; milen$> begin milen$> select into a now() - interval '$1 day'; milen$> return next a; milen$> return; milen$> end milen$> $$ language 'plpgsql'; CREATE FUNCTION milen=> No problems here. What version are you using? -- Milen A. Radev ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] Help with quotes in plpgsql
Richard Ray написа: > On Tue, 19 Dec 2006, Milen A. Radev wrote: > >> Richard Ray : >>> How should this be properly quoted >>> >>> create or replace function test(integer) returns setof text as $$ >>> declare >>> a record; >>> begin >>> select into a now() - interval '$1 day'; >>> return next a; >>> return; >>> end >>> $$ language 'plpgsql'; >>> >>> I'm not having a lot of luck >> >> >> Welcome to psql 8.2.0, the PostgreSQL interactive terminal. >> >> Type: \copyright for distribution terms >> \h for help with SQL commands >> \? for help with psql commands >> \g or terminate with semicolon to execute query >> \q to quit >> >> milen=> create or replace function test(integer) returns setof text as $$ >> milen$> declare >> milen$> a record; >> milen$> begin >> milen$> select into a now() - interval '$1 day'; >> milen$> return next a; >> milen$> return; >> milen$> end >> milen$> $$ language 'plpgsql'; >> CREATE FUNCTION >> milen=> >> >> >> >> No problems here. What version are you using? >> > > I'm using 8.1.0 but I don't think that's the problem > I have no problem creating the function but it will only substract 1 day Sorry about that - I have not understand your problem. In addition to the solution already proposed you could use "EXECUTE". See more info here - http://www.postgresql.org/docs/8.2/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN . -- Milen A. Radev ---(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] CREATE TABLE
Shavonne Marietta Wijesinghe wrote: > Hello > > From my asp page i create a table > > TableName = "CON01" > strSQL = "CREATE TABLE " & TableName & " ( ID text, N_GEN serial not > null);" > > But the problem i have is that when i go and open my database in pgadmin the > table name and coloumn name is written in lowercase :( > > How do i ask it to write them in uppercase (like in my strSQL) ? > You should quote the name. Please read more about it here - http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS Excerpt: "Quoting an identifier also makes it case-sensitive, whereas unquoted names are always folded to lower case." -- Milen A. Radev ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] Substitute
Judith написа: > Hello everybody > > somebody knows, how I can substitute in a query if a field contains a > character that I want to be shown with other character? > > for example if I have a \n I want to shows me a ~ in the result of the > select > Look for 'replace' here - http://www.postgresql.org/docs/8.2/static/functions-string.html. -- Milen A. Radev ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] Serial
Shavonne Marietta Wijesinghe wrote:
> thanks. I read the page you gave.
>
> CREATE SEQUENCE seq_mytable_n_gen;
>
> CREATE TABLE mytable
> (
> n_gen int nextval('seq_mytable_n_gen'),
> mycolumn1 int,
> mycolumn2 int
> );
>
>
> i tried creating it like that. The sequence was created without any
> error. But for the create table i get
>
> ERROR: syntax error at or near "nextval" at character 38
>
> What should i do?
Add the missing "default":
CREATE TABLE mytable
(
n_gen int DEFAULT nextval('seq_mytable_n_gen'),
mycolumn1 int,
mycolumn2 int
);
--
Milen A. Radev
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
Re: [SQL] Floating point type to store numbers
Radhika Sambamurti написа: > Hi, > I am currently using Postgresql to maintain an application which is used > for trading and back office operations. Currently our monetary fields are > stored in Varchar. I am finding a huge CPU utilization while converting > from varchar to float. I think for reasons unknown to me, we originally > stored $ amounts and rates in varchar. I am planning to convert our tables > that hold money fields and rates from varchar to float. I do not want to > convert to numeric because numeric is a special string type. > > The question is: how accurate is floating point numbers in Postgres. We > are using 7.4 soon to be moving to 8.2. > I need the accuracy to about 6 decimal points. I have read that floating > points can convert to numbers in accurately. I believe the manual is quite clear on that one ( http://www.postgresql.org/docs/current/static/datatype-numeric.html#DATATYPE-FLOAT) : " - If you require exact storage and calculations (such as for monetary amounts), use the numeric type instead." So if you decide to use floats after this warning you are on your own. -- Milen A. Radev ---(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] Floating point type to store numbers
Radhika Sambamurti написа: >> I believe the manual is quite clear on that one ( >> http://www.postgresql.org/docs/current/static/datatype-numeric.html#DATATYPE-FLOAT) >> : >> >> " - If you require exact storage and calculations (such as for monetary >> amounts), use the numeric type instead." >> >> >> So if you decide to use floats after this warning you are on your own. > > Well, actually the manual is not quite clear on this one. Given your requirements - "an application which is used for trading and back office operations." and "our monetary fields...", I still believe the manual is crystal clear. > It says quite clearly that very very small values approaching zero and > very very large values will be problematic. But I am not calculating the > distance to the moon. I need values to be accurate upto the 6th decimal > place, and was wondering if people use floating point types for this sort > of thing. -- Milen A. Radev ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] system table storing sequence attributes
Sabin Coanda написа: > ""Marcin Stкpnicki"" <[EMAIL PROTECTED]> wrote in message > news:[EMAIL PROTECTED] > ... >> I think that you either misunderstood this statement or try to break your >> application in a nasty way ;). Please tell us more about your problem >> and/or what do you want to achive, because sequences behave this way for a >> reason (concurrency issues). Perhaps there is another solution. > > I have a table with a serial primary key aoto generated by a sequence. I > add/remove records. At a moment I'd like to know what is the current value > of the sequence. I don't wish to know this in the same session where I > add/remove records. Why do you need to know that? I can't think of any reason. [...] -- Milen A. Radev ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] simple SQL question
Joshua написа: > I have a column with the following values (example below) > > 5673 > 4731 > 4462 > 5422 > 756 > 3060 > > I want the column to display the numbers as follows: > > 56.73 > 47.31 > 44.62 > 54.22 > 7.56 > 30.60 > > I have been playing around with string functions but cannot seem to > figure out a quick solution. Does anyone have any suggestions? Use "to_char(int, text)", details here - http://www.postgresql.org/docs/current/static/functions-formatting.html -- Milen A. Radev ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Table-name as parameter to plpgsql
Andreas Joseph Krogh написа:
The following function failes to compile:
CREATE OR REPLACE FUNCTION test_func(p_table_name VARCHAR) RETURNS VOID AS $$
BEGIN
INSERT INTO p_table_name(some_field) VALUES('some_value');
END;
$$ LANGUAGE plpgsql;
Gives:
ERROR: syntax error at or near "$1"
LINE 1: INSERT INTO $1 (some_field) VALUES('some_value')
Any hints on how to use function-parameters as table-names like I'm trying to
above?
Use EXECUTE
(http://www.postgresql.org/docs/current/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN).
--
Milen A. Radev
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [SQL] How to use serial variable to insert into muiti-recrods?
hu js написа: > run: > "CREATE TABLE xxx > ( > id serial NOT NULL, > name character varying > ); > insert into xxx select default values,place_name from air_bui;" insert into xxx (name) select place_name from air_bui; > > fail: > "ERROR: syntax error at or near "default" > SQL state: 42601 > Character: 24" > -- Milen A. Radev ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] PL/pgSQL question
Sebastian Ritter написа: Hi all, I have a question regarding functions. How can I return zero rows from a function whose return type is a table row? I did the following test and it did not work as expected: [...] CREATE OR REPLACE FUNCTION foobar(boolean) RETURNS SETOF x AS $_$ DECLARE res x%ROWTYPE; BEGIN if $1 THEN RETURN NEXT res; ELSE RETURN; END IF; END;$_$ LANGUAGE plpgsql; -- Milen A. Radev ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] comment on COLUMN, broken or misunderstanding?
Bryce Nesbitt написа: I'm expecting COLUMN comments to work much like table comments, but I'm getting nothing back. Is this a reportable bug, or a misunderstanding? [...] Misunderstanding I would say - "\dd" is for objects and they are: '"Object" covers aggregates, functions, operators, types, relations (tables, views, indexes, sequences, large objects), rules, and triggers.' (http://www.postgresql.org/docs/8.2/static/app-psql.html, look for "\dd"). You could see the comment for the column with "\d+ table_name". -- Milen A. Radev ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] Re: Get day name(Mon, Tue... Sun) and day number (1, 2...7) from a date
Emi Lu написа:
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?)
You need "TO_CHAR"
(http://www.postgresql.org/docs/current/static/functions-formatting.html)
- "SELECT to_char(current_date, 'Dy')".
--
Milen A. Radev
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] psql: FATAL: Ident authentication failed for user "postgres"
VG написа: > Hi, > > I have installed postgresql-serve using yum > hba.conf group and owner were postgres then i changed it to root. Why? > > when i type psql -U postgres command ( as root user) I get: > > psql: FATAL: Ident authentication failed for user "postgres" Most probably the default pg_hba.conf includes the following line: local all postgres ident Which means that you could login with the DB user "postgres" only if you are logged in already as the system user "postgres". So "su - postgres" before using psql should do the trick. Please read "Chapter 21. Client Authentication" (http://www.postgresql.org/docs/current/static/client-authentication.html) for details. -- Milen A. Radev -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] How to provide password to pg_dump command ?
Emi Lu написа: > Good morning, > > Is there a way that I can pass "pwd" to pg_dump command please? > > > I tried to pg_dump in java, but do not know how to pass password. Recommended method - http://www.postgresql.org/docs/current/static/libpq-pgpass.html Not recommended method - http://www.postgresql.org/docs/current/static/libpq-envars.html (look for PGPASSWORD). -- Milen A. Radev -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Re: How to insert Images(bnp,png,etc) into Postgresql and how to retrive the inserted Imaged using C#.net
venkat написа: > Dear All, > > I want to insert Images(bnp,png,etc) into Postgresql and how to retrieve > the inserted Imaged using C#.net.I do not know where to start? Any one can > help me. [...] Start here - http://npgsql.projects.postgresql.org/docs/manual/UserManual.html and look for "Working with binary data and bytea datatype". -- Milen A. Radev -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Variable number or arguments to a function possible?
Chris Ruprecht написа: > Hello everybody, > > Is it possible to create a function that can take a variable number of > arguments? > I would like to write a function that creates a new record in the > database. Based on what I send it, it should create a record in the > appropriate table. > > Simple pseudo-code example: > > ... function create_record( varchar [,...] ) returns bigint as > > if $1 = 'state' then insert into state ( $2, $3 ) // $2 being state > name, and $3 state code > if $1 = 'phone' then insert into phone ( $4::bigint, $2, $3 ) // $2 = > phone number, $3 = phone type, $4 = id of person that ownes the phone > > and so on. > > How would I declare that function? You'll be able to do that (or something similar) in the next, still in beta, version 8.4 (http://www.postgresql.org/docs/8.4/static/xfunc-sql.html#XFUNC-SQL-VARIADIC-FUNCTIONS), but right now the closest to what you want is function overloading (http://www.postgresql.org/docs/current/static/xfunc-overload.html). -- Milen A. Radev -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] RAISE NOTICE
Luigi N. Puleio написа: > Sort ofthe message should come along a standalone application too when > with an ADO component I do a Post() which calls the function...just the > matter is I can't modify the application to manage a possibly > notification...only the postgresql's function... > That's why I'm asking about a possibility to get a notification from the > function like it's an EXCEPTION... [...] I believe the fine manual is clear about this (http://www.postgresql.org/docs/current/static/plpgsql-errors-and-messages.html): "Whether messages of a particular priority are reported to the client, written to the server log, or both is controlled by the log_min_messages and client_min_messages configuration variables. See Chapter 18 for more information." -- Milen A. Radev -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
