[SQL] plpgsql - Insert from a record variable?

[Phil Endecott]

Dear Postgresql experts,

I'm writing a simple plpgsql function that selects a row from a table, modifies it 
slightly, and inserts the modified version.  This sounds simple, but I can't find how 
to do the insert in a simple generic fashion: although SELECT has a form that puts the 
result in a record variable, INSERT doesn't seem to have anything similar.

What I'd like to be able to write is something like this:

DECLARE
  R RECORD;
BEGIN
  SELECT * INTO R FROM TABLE WHERE id=n;
  R.something := x;
  INSERT INTO TABLE R;
END

But as far as I can see the only syntax that is allowed for INSERT is the normal 
INSERT INTO TABLE (x,y,z) VALUES (a,b,c).  To achieve what I want to do I'd need to 
iterate over the fields of the record (how?) and build up the string of the INSERT 
query.

It seems odd that SELECT can use a record variable but INSERT can't, so I wonder if I 
have missed something.  Any suggestions?

Thanks in advance for any help anyone can offer.

Regards,

--Phil.

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

[SQL] Inheritance, plpgsql inserting, and revisions

[Phil Endecott]

  on (td.tableoid=pc.oid) where td.id=del_id;
if not found then
  raise exception ''object % not found'', del_id;
end if;
-- following "loop" executes once.
for r in execute ''select * from '' || table {without the _d}
  || '' where id='' || del_id
loop
  r.deleted := t;
  r.editdate := current_timestamp;
  insert into table r;  !!! Nope !!!
  exit;
end loop;
  end;
' language plpgsql;


As you can see, I have got as far as reading the row from the derived table (M or N) 
into a record variable r, and have modified it.  Now I want to insert this value back 
into the table.  The syntax I was hoping to find is something like INSERT r INTO 
table, but it doesn't seem to exist.  So maybe I have to construct an explicit INSERT 
(...) VALUES (...) statement as a string.  That's OK, but is there an "introspection 
mechanism" that lets me iterate over the fields of a record, getting their names?

I feel I'm pretty close to having a neat solution to an interesting problem, but am 
stuck with this bit of plpgsql syntax.  Can anyone offer any suggestions?

Thank you for reading this far!

Regards,

--Phil Endecott.

p.s. My spellcheker wants to turn plpgsql into "popsicle"!  What a great idea on a 
sunny afternoon like today...

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

Re: [SQL] Function Parameters - need help !!!

[Phil Endecott]

Hi,

Quote from Section 37.11 of the manual:

# There are no default values for parameters in PostgreSQL.
# You can overload function names in PostgreSQL. This is often used
  to work around the lack of default parameters.

So for your example:

> CREATE FUNCTION test(integer,integer) RETURNS INTEGER AS '
> ...

you should be able to write:

CREATE FUNCTION test(integer) RETURNS INTEGER AS '
BEGIN
  test($1, default_value_for_param2);
END;
' LANGUAGE 'plpgsql';

and also:

CREATE FUNCTION test() RETURNS INTEGER AS '
BEGIN
  test(default_value_for_param1);
END;
' LANGUAGE 'plpgsql';


Hope this is what you were looking for.

--Phil.

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] plpgsql - Insert from a record variable?

[Phil Endecott]

Phil> Insert from a record variable? 

Riccardo> Try   insert into table select r.*;

Tom> in 7.5 
Tom>   insert into table select r.*;
Tom>   insert into table values(r.*);
Tom> so long as r is declared to be of a named rowtype (not just
Tom> RECORD)


Thanks!  Unfortunately I need record, rather than %rowtype.  See my later email where 
I describe how I am trying to use this with inheritance; the function looks up a row 
in a base table, finds the derived table in which it really exists using 
pg_class.relname, and then inserts a modified copy of the row in the derived table.

I'm not concerned about the performance issues to do with pre-planning the queries.  I 
think what I really need is an introspection mechanism so that I can loop over each 
element of the record and construct the insert as a string.  Maybe this is possible 
using a different server-side language?  I've just had an idea: perhaps rather than 
inspecting the record variable to see what fields it contains, I can look at the table 
to see what columns it contains (since this amounts to the same thing).  Presumably I 
can do this using information_schema.columns.  I'll have a go.

--Phil.
 

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [SQL] plpgsql - Insert from a record variable?

[Phil Endecott]

I wrote:
> perhaps rather than inspecting the record variable to see what fields
> it contains, I can look at the table to see what columns it contains

This is my effort.  It doesn't work, because I can only EXECUTE
SQL statements, not PLPGSQL statements.  I need to EXECUTE an
assignment statement to accumulate the string of column values.

I have a feeling that I can EXECUTE a CREATE FUNCTION statement,
and then call the function, but this seems over-the-top.  I just
want to insert a record into a table!  Any better ideas?

-- Simply insert record r into table t.

-- Doesn't work, because EXECUTE takes an SQL command, not
--   a plpgsql statement.

create function insert_record ( record, text ) as '
-- probably ought to pass schema as well as table name, since
-- information_schema.columns query doesn't use search_path.
declare
  r as alias for $1;
  t as alias for $2;

  cr information_schema.columns%rowtype;
  first boolean;
  column_names text;
  column_values text;
begin

  first := true;
  for cr in select * from information_schema.columns
where table_name=t loop
if not first then
  column_names := column_names || '', '';
  column_values := column_values || '', '';
  first := false;
end if;
column_names := column_names || quote_ident(cr.column_name);
!!  execute ''column_values := 
!! column_values || quote_literal(r.'' || cr.column_name || '')'';
  end loop;

  execute ''insert into '' || t || ''('' || column_names ||
  '') values ('' || column_values || '')'';

end;
' language plpgsql;



--Phil.

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

Re: [SQL] plpgsql - Insert from a record variable?

[Phil Endecott]

Phil> execute ''column_values :=
Phil>  column_values || quote_literal(r.'' || cr.column_name || '')'';

basic> FOR rec IN EXECUTE
basic>   ''select column_values || quote_literal(r.'' ||
basic>   cr.column_name || '') alias column_values''; LOOP
basic>  column_values := rec.column_values;
basic> END LOOP;

I think your code will try to execute a query like this:

select column_values || quote_literal(r.something) alias column_values

This will fail because column_values and r are both plpgsql variables, and so are not 
visible to the SQL interpreter.

Any other suggestions?

--Phil.

---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [SQL] Normalising an existing table - how?

[Phil Endecott]

Graham Leggett wrote:
> I have an existing table containing a column in it containing
> a money value. I would like to normalise this column into a
> separate table, as the money value is to be expanded to contain
> a tax value, etc.
> 
> I have been trying to find a SQL query that will do the
> following:
> 
> - Select the money column from the table
> - Populate the new normalised table with each row containing
>   the value from the original money column
> - Write the primary keys of the new rows in the normalised
>   table, back to a new column in the original table added for
>   this purpose.

Change the order.  Do the third step first:

alter table T add column X integer;
update T set X = nextval(somesequence);

Now do the first and second steps together:

select X, MoneyColumn from T into NewTable;

Is this the sort of thing you need?

--Phil.


---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [SQL] Normalising an existing table - how?

[Phil Endecott]

Graham Leggett <[EMAIL PROTECTED]> wrote:
> >>- Select the money column from the table
> >>- Populate the new normalised table with each row containing
> >>  the value from the original money column
> >>- Write the primary keys of the new rows in the normalised
> >>  table, back to a new column in the original table added for
> >>  this purpose.
> 
> > Change the order.  Do the third step first:
> > 
> > alter table T add column X integer;
> > update T set X = nextval(somesequence);
> > 
> > Now do the first and second steps together:
> > 
> > select X, MoneyColumn from T into NewTable;
> > 
> > Is this the sort of thing you need?
> 
> I think it is - though the select foo into NewTable part, does
> NewTable have to be empty first, or can it already exist?
> 
> In my case NewTable has some rows in it already, as the database is 
> currently partially normalised - I need to finish the job.

Check the docs.  I believe that SELECT INTO does the same as CREATE TABLE AS, i.e. it 
creates a new table.  It will presumably fail if the table already exists.  You 
probably need INSERT SELECT, i.e.

  insert into NewTable select X, MoneyColumn from T;


--Phil.

---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

[SQL] Need indexes on inherited tables?

[Phil Endecott]

Dear Postgresql experts,

I have a base table that declares a primary key spanning a couple of columns:

create table B (
  id integer,
  xx someothertype,
  .
  primary key (id, xx)
);

and a number of derived tables that inherit from B:

create table T (
 
) inherits (B);

An index is automatically created for B because of the primary key.

If I search for something in T using the key columns, e.g. I do

  select * from T where id=1 and xx=something;

will the index be used?  Or must I explicity create an index on id and xx for T and 
each of the other derived tables?

Is it any different if I search in B and find rows that are actually in T?

(Slightly unrelated: does the index on (id,xx) help when I am searching only on id?)

Thanks for any insight anyone can offer.

--Phil.

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [SQL] Need indexes on inherited tables?

[Phil Endecott]

I asked if derived tables use primary key indexes generated in the base tables that 
they inherit from.

Franco Bruno Borghesi replied:
> [the derived table] is not inheriting any indexes, neither the
> primary key constraint.

OK, thanks!  I had come to the conclusion that it was not using the index, but I'm 
really surprised to hear that the primary key constraint that I define in a base table 
is not inherited.  Are any constraints inherited?  What happens if I declare a 
single-column primary key?  What if I declare a "not null" constraint or a "check" 
constraint in a base table?

Having to replicate the constraints and indexes for each derived table is a pain - 
lots of error-prone typing - but there is a more serious problem: how can I ensure 
that these keys are unique across all of the derived tables?  (i.e. if T1 and T2 
inherit from B, and B's primary key is (id,xx), then I want there to be at most one 
row in (T1 union T2) that has any value of (id,xx).)

Is this a possible area for future enhancements?

Regards,

--Phil.

 

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [SQL] finding if a foreign key is in use

[Phil Endecott]

Kenneth Gonsalves <[EMAIL PROTECTED]> wrote:
> in my app i have a table where the id serves as a foreign key for
> one or more other tables. if i want to delete a row in the table,
> i currently search the other tables where this table is referenced
> to see if the row is in use - and then, if not in use, permit
> deletion.

You can automate this check by declaring the foreign keys like this:

id some_type references other_table(id) on delete no action

The "on delete no action" bit means "if you try to delete the row in the referred-to 
table (where it is the primary key), cause an error.  The alternative is "on delete 
cascade", which means that rows in the referring tables are deleted if the row that 
they refer to is deleted.

> Now if i want the delete button in my app to be disabled whenever
> a row that is in use is selected, searching the database every time
> would dramatically slow down the app.

Basically you do have to do this search.  But it won't be too slow if you create an 
index on the foreign key.

I would probably do it like this.  Here is the first table:

create table T1 (
  id   integer   primary key,
  xtext
);

Here is the second table that refers to it:

create table T2 (
  id   integer   references T1.id on delete no action,
  ytext
);

So that the searches can be efficient, we create an index:

create index T2_by_id on T2(id);

Now I would create a view that adds an extra column to T1, indicating whether any rows 
in T2 refer to it:

create view T1_v as
  select *, id in (select id from T2) as cannot_delete
  from T1;

But beware!  It seems that this particular form DOESN'T use the index we've just 
created.  On the other hand, this very similar one does:

create view T1_v as
  select *, exists (select * from T2 where id=t.id) as cannot_delete
  from T1 t;

Now, when you create your user interface, you can just look at the cannot_delete field 
to see whether the delete button should be enabled.

This should run in logarithmic time.  If this isn't fast enough you could instead make 
cannot_delete a real column and have triggers on changes to T2 that change its value.  
But I wouldn't resort to this unless you are really desperate.

Regards,

--Phil.


---(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] finding if a foreign key is in use

[Phil Endecott]

in my app i have a table where the id serves as a foreign key for
one or more other tables. if i want to delete a row in the table,
i currently search the other tables where this table is referenced
to see if the row is in use - and then, if not in use, permit
deletion.
Now if i want the delete button in my app to be disabled whenever
a row that is in use is selected, searching the database every time
would dramatically slow down the app.
Basically you do have to do this search.  But it won't be too slow if you
create an index on the foreign key.
pity. thought postgres would have some function like 'in_use' to tell when a 
row that is used as a foreign key is in actual use and hence cannot be 
deleted. surely, in a database of millions of records, it wouldnt have search 
them all to find if the row is in use?
It doesn't "search them all" if you have an index.  If your database has 
a million records it needs to look at only 20 index entries, as 2^20 is 
about a million. (At least that's what I, naively, think it should do - 
anyone who knows more want to correct me?)

--Phil.
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [SQL] please help me with text cast to int ....

[Phil Endecott]

Theodore,
Because jobnumber is declared as text, you are getting "dictionary 
order" (lexicographic) ordering on the values.  In a dictionary, "abc" 
comes after "a", obviously.  So indeed "999" will come after 
"1000".  To get the effect that you want you need to treat jobnumber as 
a number.  The easiest thing to do would be to change the declaration of 
the table.  If for some reason you can't do that, you need to do a cast 
in the query; that would make your WHERE expression work, but I don't 
know about ORDER BY (look it up).

For example:
SELECT jobnumber, jobtitle FROM jobinfo WHERE
jobnumber::integer >= 200 ORDER BY jobnumber ASC;
Do you ever have non-numeric values in the jobnumber field?  (Is that 
why it's declared as text?)  If you do you will get problems because 
they cannot be converted to integers in order to perform the comparison.

Regards,
--Phil.
Theodore Petrosky wrote:
I give up.. what don't I understand about casting and
ints and text..  

i have a table jobinfo with:
acode text,
jobnumber text default
nextval('public.jobinfo_seq'::text),
jobtitle text
I have about 3000 rows starting with jobnumber = 1000.
SELECT jobnumber, jobtitle FROM jobinfo WHERE
jobnumber >= 999 ORDER BY jobnumber ASC;
The above SQL produces no rows. however...
SELECT jobnumber, jobtitle FROM jobinfo WHERE
jobnumber >= 200 ORDER BY jobnumber ASC;
produces rows with jobnumber >= 2000
if I change the query with jobnumber >= 201, I get
rows >= 2010.
it is as if there was a silent zero being appended to
the end of my int in the query. What am I missing,
please.

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings