[SQL] copy rows
Hi, What is the simplest solution in plpgsql to copy some rows in a table? I would like to do something like: select some rows do for each row skip serial field \ modify one field |-- what is the simplest way for this? insert as new row / Is it possible to insert a record type variable? If yes, how to skip a field? Daniel ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] parsing a string with a hexadecimal notation
I intend to retrieve an int value in an integer variable from a string with
a hexadecimal notation of a number.
Which function is appropriate to do
i int4
i = ???('BEAF')
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] parsing a string with a hexadecimal notation
O KΦPFERL Robert έγραψε στις Feb 9, 2005 :
> I intend to retrieve an int value in an integer variable from a string with
> a hexadecimal notation of a number.
> Which function is appropriate to do
> i int4
> i = ???('BEAF')
You can do something like
foodb=# SELECT int4(X'FF'::bit varying);
int4
--
255
(1 row)
but i suspect you must prepare your statement out of sql.
E.g. in java:
String hex="FF";
st = con.prepareStatement("select int4(X'"+hex+"'::bit varying");
>
> ---(end of broadcast)---
> TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
>
--
-Achilleus
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
[SQL] How to iterate through arrays?
Hi there, I'm trying to iterate through arrays in PL/PGSQL: DECLARE update_query CHAR; update_query_params CHAR ARRAY[6]; BEGIN update_query_params[1]:='some text'; RAISE NOTICE 'Testing element %', update_query_params[1]; END . It does not 'compile'... :-(. Can you tell me what is the problem? Thanks, Nosy ---(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] How to iterate through arrays?
- use TEXT instead of CHAR (what is CHAR without (n) ?)
- inintialize your array with '{}' because it is created as NULL if you
just declare it without setting it to an empty array.
Hi there,
I'm trying to iterate through arrays in PL/PGSQL:
DECLARE
update_query CHAR;
update_query_params CHAR ARRAY[6];
BEGIN
update_query_params[1]:='some text';
RAISE NOTICE 'Testing element %',
update_query_params[1];
END
.
It does not 'compile'... :-(. Can you tell me what is the problem?
Thanks,
Nosy
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
---(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] How to iterate through arrays?
I think the problem is not there. I got the following error:
'ERROR: syntax error at or near "[" at character 1234', the line is: RAISE
NOTICE '% ...',update_query_params[1];
May be someone give a code sample...
Thanks
Pe data de Mie 09 Feb 2005 16:16, ati scris:
> - use TEXT instead of CHAR (what is CHAR without (n) ?)
> - inintialize your array with '{}' because it is created as NULL if you
> just declare it without setting it to an empty array.
---(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] How to iterate through arrays?
On Feb 9, 2005, at 9:26 AM, NosyMan wrote: I think the problem is not there. I got the following error: 'ERROR: syntax error at or near "[" at character 1234', the line is: RAISE NOTICE '% ...',update_query_params[1]; The problem here is RAISE, NOTICE, etc. can only use simple variables in the format string. Try it like: myTextVar := update_query_params[1]; NOTICE '% ...',myTextVar; John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] parsing a string with a hexadecimal notation
Achilleus Mantzios <[EMAIL PROTECTED]> writes: > You can do something like > foodb=# SELECT int4(X'FF'::bit varying); > int4 > -- > 255 > (1 row) > but i suspect you must prepare your statement out of sql. No, I think you could do it with a placeholder if you wanted. The secret is the (poorly documented) external syntax for a hex bit string: regression=# select 'xBEEF'::bit varying; varbit -- 10101110 (1 row) So it should work to do "SELECT int4($1::bit varying)" and then pass 'xBEEF' as the string value for the parameter. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] Function .. AS..?
Those instructions are good but i want to call this function only for " select consulta_contacto(1)" nothing more, Is really necesary to use "AS ..."? this is the call: select * from consulta_contacto(1) as (cont_codigo integer, cont_idpers varchar, cont_nombre varchar, cont_apellido varchar, cont_titulo varchar, cont_fecnac timestamp , cont_codedociv integer, cont_sexo char(1), cont_codpais integer, cont_pw varchar, cont_empr varchar, cont_cargo varchar, cont_pwempr varchar, cont_aniv char(5), cont_prof varchar, cont_trab char(1), cont_fecgen timestamp, cont_fecing timestamp, cont_fuente char(1), cont_sinc char(1), cont_codupload integer); this is the function: CREATE OR REPLACE FUNCTION consulta_contacto(integer) RETURNS SETOF RECORD AS 'DECLARE rec RECORD; sup INTEGER;BEGIN FOR rec IN SELECT cont_codigo,cont_idpers, cont_nombre,cont_apellido,cont_titulo,cont_fecnac,cont_codedociv,cont_sexo,cont_codpais,cont_pw,cont_empr,cont_cargo,cont_pwempr,cont_aniv,cont_prof,cont_trab,cont_fecgen,cont_fecing,cont_fuente,cont_sinc,cont_codupload FROM tbu_contacto LOOP RETURN NEXT rec ; END LOOP; RETURN ;END;' LANGUAGE plpgsql;
Re: [SQL] [HACKERS] Function .. AS..?
On Wed, 9 Feb 2005, Ing. Jhon Carrillo wrote: > Those instructions are good but i want to call this function only for " > select consulta_contacto(1)" nothing more, Is really necesary to use > "AS ..."? If it absolutely needs to be setof record, yes. It may be more appropriate to make a composite type with CREATE TYPE AS and then make the function return SETOF instead. ---(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 .. AS..?
On Wed, Feb 09, 2005 at 04:57:14PM -0400, Ing. Jhon Carrillo wrote: > Those instructions are good but i want to call this function only > for " select consulta_contacto(1)" nothing more, Is really necesary > to use "AS ..."? > > this is the call: > > select * from consulta_contacto(1) as (cont_codigo integer, > cont_idpers varchar, > ... Instead of declaring the function to return SETOF RECORD, you could return SETOF typename, where "typename" is the name of a composite type or a table; your queries could then omit "AS " -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
